我如何参数化一个包含有可变数量参数的IN子句的查询,就像这样?

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

在这个查询中,参数的数量可以是1到5之间的任意值。

我不喜欢使用专门的存储过程(或XML),但如果有一些特定于SQL Server 2008的优雅方式,我愿意接受。


当前回答

这是一个解决同样问题的交叉帖子。比保留分隔符更健壮-包括转义和嵌套数组,并理解null和空数组。

c# & T-SQL string[]打包/解包实用函数

然后可以连接到表值函数。

其他回答

使用动态查询。前端只生成所需的格式:

DECLARE @invalue VARCHAR(100)
SELECT @invalue = '''Bishnu'',''Gautam'''

DECLARE @dynamicSQL VARCHAR(MAX)
SELECT @dynamicSQL = 'SELECT * FROM #temp WHERE [name] IN (' + @invalue + ')'
EXEC (@dynamicSQL)

SQL小提琴

我听到Jeff/Joel今天在播客上谈论这个(第34集,2008-12-16 (MP3, 31 MB), 1小时03分38秒-1小时06分45秒),我想我记得Stack Overflow使用LINQ到SQL,但也许它被抛弃了。在LINQ to SQL中也是如此。

var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };

var results = from tag in Tags
              where inValues.Contains(tag.Name)
              select tag;

就是这样。而且,是的,LINQ已经足够向后看了,但是Contains子句对我来说似乎太向后看了。当我不得不在工作中为一个项目做类似的查询时,我自然地试图用错误的方式来做这个,在本地数组和SQL Server表之间做一个连接,认为LINQ to SQL翻译器将足够聪明,以某种方式处理翻译。它没有这样做,但它确实提供了一个描述性的错误消息,并指示我使用Contains。

无论如何,如果您在强烈推荐的LINQPad中运行此命令,并运行此查询,您可以查看SQL LINQ提供程序生成的实际SQL。它将向您显示每个参数化为IN子句的值。

这是一个解决同样问题的交叉帖子。比保留分隔符更健壮-包括转义和嵌套数组,并理解null和空数组。

c# & T-SQL string[]打包/解包实用函数

然后可以连接到表值函数。

步骤1:-

string[] Ids = new string[] { "3", "6", "14" };
string IdsSP = string.Format("'|{0}|'", string.Join("|", Ids));

步骤2:-

@CurrentShipmentStatusIdArray [nvarchar](255) = NULL

步骤3:-

Where @CurrentShipmentStatusIdArray is null or @CurrentShipmentStatusIdArray LIKE '%|' + convert(nvarchar,Shipments.CurrentShipmentStatusId) + '|%'

or

Where @CurrentShipmentStatusIdArray is null or @CurrentShipmentStatusIdArray LIKE '%|' + Shipments.CurrentShipmentStatusId+ '|%'

在我看来,解决这个问题的最佳来源是这个网站上发布的内容:

Syscomments。Dinakar Nethi

CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (col1 varchar(50))
AS 
BEGIN
 --DECLARE @T Table (col1 varchar(50))  
 -- @Array is the array we wish to parse
 -- @Separator is the separator charactor such as a comma
 DECLARE @separator_position INT -- This is used to locate each separator character
 DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
 -- For my loop to work I need an extra separator at the end. I always look to the
 -- left of the separator character for each array value

 SET @array = @array + @separator

 -- Loop through the string searching for separtor characters
 WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
 BEGIN
    -- patindex matches the a pattern against a string
    SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
    SELECT @array_value = LEFT(@array, @separator_position - 1)
    -- This is where you process the values passed.
    INSERT into @T VALUES (@array_value)    
    -- Replace this select statement with your processing
    -- @array_value holds the value of this element of the array
    -- This replaces what we just processed with and empty string
    SELECT @array = STUFF(@array, 1, @separator_position, '')
 END
 RETURN 
END

Use:

SELECT * FROM dbo.fnParseArray('a,b,c,d,e,f', ',')

致谢:Dinakar Nethi