我如何参数化一个包含有可变数量参数的IN子句的查询，就像这样?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中，参数的数量可以是1到5之间的任意值。
我不喜欢使用专门的存储过程(或XML)，但如果有一些特定于SQL Server 2008的优雅方式，我愿意接受。
当前回答
在ColdFusion中，我们只需要:
<cfset myvalues = "ruby|rails|scruffy|rubyonrails">
<cfquery name="q">
select * from sometable where values in <cfqueryparam value="#myvalues#" list="true">
</cfquery>
其他回答
使用动态查询。前端只生成所需的格式:
DECLARE @invalue VARCHAR(100)
SELECT @invalue = '''Bishnu'',''Gautam'''
DECLARE @dynamicSQL VARCHAR(MAX)
SELECT @dynamicSQL = 'SELECT * FROM #temp WHERE [name] IN (' + @invalue + ')'
EXEC (@dynamicSQL)
SQL小提琴
这是一个解决同样问题的交叉帖子。比保留分隔符更健壮-包括转义和嵌套数组，并理解null和空数组。
c# & T-SQL string[]打包/解包实用函数
然后可以连接到表值函数。
下面是一种技术，用于重新创建查询字符串中使用的本地表。这样做可以消除所有解析问题。
字符串可以用任何语言构建。在本例中，我使用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)
如果在IN子句中存储了以逗号(，)分隔的字符串，则可以使用charindex函数获取值。如果使用. net，则可以使用SqlParameters进行映射。
DDL Script:
CREATE TABLE Tags
([ID] int, [Name] varchar(20))
;
INSERT INTO Tags
([ID], [Name])
VALUES
(1, 'ruby'),
(2, 'rails'),
(3, 'scruffy'),
(4, 'rubyonrails')
;
t - sql:
DECLARE @Param nvarchar(max)
SET @Param = 'ruby,rails,scruffy,rubyonrails'
SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0
您可以在. net代码中使用上述语句并将参数映射为SqlParameter。
提琴手演示
编辑: 使用下面的脚本创建名为SelectedTags的表。
DDL Script:
Create table SelectedTags
(Name nvarchar(20));
INSERT INTO SelectedTags values ('ruby'),('rails')
t - sql:
DECLARE @list nvarchar(max)
SELECT @list=coalesce(@list+',','')+st.Name FROM SelectedTags st
SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0
可以将参数作为字符串传递
这是弦
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
