SQL Server 在关键字传递字符串数组查询中使用 [英] SQL Server using in keyword pass string array query

查看:39
本文介绍了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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆