SQL Server使用in关键字传递字符串数组查询

小编典典

SQL Server使用in关键字传递字符串数组查询

sql

我不认为IN子句可以接受具有多个值的绑定参数。甲骨文不能和几分钟

和查询是

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

结果为空

并直接在关键字中传递字符串

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

那结果还可以

阅读 401

2021-04-07

共1个答案

小编典典

在SQL Server 2016+中：使用string_split内置函数。请注意，不再需要多余的单引号：

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的答案是一种更好的方法

2021-04-07