SQL Server 在关键字传递字符串数组查询中使用 [英] SQL Server using in keyword pass string array query
本文介绍了SQL Server 在关键字传递字符串数组查询中使用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我认为 IN 子句不能接受具有多个值的绑定参数.Oracle 不能和几分钟
I don't think the IN clause can accept bind parameters with multiple values. Oracle can't and a couple minutes
和查询是
declare @setting varchar(max)
set @setting ='''Sales Entry Grid Cursor'',''Customer Mandatory'',''Column Uom'',''Show Marka'',''Show Discount Amount In Grid'',''Show Discount % In Grid'',''Calculation based on Weight *rate'''
和存储过程是
Select pageconfig_action
From [RetailSoft].[dbo].[tbl_pageconfig]
Where [PageConfig_settingsName] in (@setting)
and PageConfig_CompanyId = 1
结果为空
并直接在关键字中传入字符串
And pass string in directly in keyword
Select pageconfig_action
From [RetailSoft].[dbo].[tbl_pageconfig]
Where [PageConfig_settingsName] in ('Sales Entry Grid Cursor', 'Customer Mandatory', 'Column Uom', 'Show Marka', 'Show Discount Amount In Grid', 'Show Discount % In Grid', 'Calculation based on Weight *rate')
and PageConfig_CompanyId=1
结果没问题
推荐答案
在 SQL Server 2016+ 中:使用 string_split
内置函数.请注意,不再需要额外的单引号:
In SQL Server 2016+: using string_split
built in function. Please note, that extra single quotes are not necessary anymore:
DECLARE @setting varchar(max)
set @setting ='Sales Entry Grid Cursor,Customer Mandatory,Column Uom,Show Marka,Show Discount Amount In Grid,Show Discount % In Grid,Calculation based on Weight *rate'
Select pageconfig_action from [RetailSoft].[dbo].[tbl_pageconfig]
Where [PageConfig_settingsName] in(SELECT value FROM string_split(@setting, ',') )
and PageConfig_CompanyId=1
如果您运行的 SQL Server 早于 SQL 2016,@GuidoG 的答案是一种更可取的方法
If you run SQL Server older than SQL 2016, the answer of @GuidoG is a preferable method
这篇关于SQL Server 在关键字传递字符串数组查询中使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文