我如何参数化一个包含有可变数量参数的IN子句的查询,就像这样?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中,参数的数量可以是1到5之间的任意值。
我不喜欢使用专门的存储过程(或XML),但如果有一些特定于SQL Server 2008的优雅方式,我愿意接受。
我如何参数化一个包含有可变数量参数的IN子句的查询,就像这样?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中,参数的数量可以是1到5之间的任意值。
我不喜欢使用专门的存储过程(或XML),但如果有一些特定于SQL Server 2008的优雅方式,我愿意接受。
当前回答
对于这样数量可变的参数,我所知道的唯一方法是显式地生成SQL,或者做一些涉及用所需项填充临时表并与临时表连接的事情。
其他回答
下面是一种技术,用于重新创建查询字符串中使用的本地表。这样做可以消除所有解析问题。
字符串可以用任何语言构建。在本例中,我使用SQL,因为这是我试图解决的原始问题。我需要一种干净的方法来在一个字符串中传递表数据,以便稍后执行。
使用用户定义的类型是可选的。创建类型只创建一次,并且可以提前完成。否则,只需在字符串中的声明中添加一个完整的表类型。
通用模式易于扩展,可用于传递更复杂的表。
-- Create a user defined type for the list.
CREATE TYPE [dbo].[StringList] AS TABLE(
[StringValue] [nvarchar](max) NOT NULL
)
-- Create a sample list using the list table type.
DECLARE @list [dbo].[StringList];
INSERT INTO @list VALUES ('one'), ('two'), ('three'), ('four')
-- Build a string in which we recreate the list so we can pass it to exec
-- This can be done in any language since we're just building a string.
DECLARE @str nvarchar(max);
SET @str = 'DECLARE @list [dbo].[StringList]; INSERT INTO @list VALUES '
-- Add all the values we want to the string. This would be a loop in C++.
SELECT @str = @str + '(''' + StringValue + '''),' FROM @list
-- Remove the trailing comma so the query is valid sql.
SET @str = substring(@str, 1, len(@str)-1)
-- Add a select to test the string.
SET @str = @str + '; SELECT * FROM @list;'
-- Execute the string and see we've pass the table correctly.
EXEC(@str)
可以将参数作为字符串传递
这是弦
DECLARE @tags
SET @tags = ‘ruby|rails|scruffy|rubyonrails’
select * from Tags
where Name in (SELECT item from fnSplit(@tags, ‘|’))
order by Count desc
然后你所要做的就是将字符串作为1参数传递。
这是我使用的分裂函数。
CREATE FUNCTION [dbo].[fnSplit](
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
对于这样数量可变的参数,我所知道的唯一方法是显式地生成SQL,或者做一些涉及用所需项填充临时表并与临时表连接的事情。
在我看来,解决这个问题的最佳来源是这个网站上发布的内容:
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
这很恶心,但如果你保证至少有一个,你可以这样做:
SELECT ...
...
WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )
有IN('tag1', 'tag2', 'tag1', 'tag1', 'tag1')将很容易被SQL Server优化掉。另外,你可以直接搜索索引