我如何参数化一个包含有可变数量参数的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的优雅方式,我愿意接受。
当前回答
有一个很好的、简单的、经过测试的方法:
/* Create table-value string: */
CREATE TYPE [String_List] AS TABLE ([Your_String_Element] varchar(max) PRIMARY KEY);
GO
/* Create procedure which takes this table as parameter: */
CREATE PROCEDURE [dbo].[usp_ListCheck]
@String_List_In [String_List] READONLY
AS
SELECT a.*
FROM [dbo].[Tags] a
JOIN @String_List_In b ON a.[Name] = b.[Your_String_Element];
我已经开始使用这种方法来修复实体框架的问题(对于我们的应用程序来说不够健壮)。所以我们决定给Dapper(和Stack一样)一个机会。还指定您的字符串列表为表与PK列修复您的执行计划很多。 这是一篇关于如何将一个表转换为Dapper的好文章——都是快速而干净的。
其他回答
如果你从。net调用,你可以使用Dapper dot net:
string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = dataContext.Query<Tags>(@"
select * from Tags
where Name in @names
order by Count desc", new {names});
这里是达普在思考,所以你不用思考。当然,类似的事情也可能发生在LINQ to SQL中:
string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = from tag in dataContext.Tags
where names.Contains(tag.Name)
orderby tag.Count descending
select tag;
在SQL Server 2016+中,另一种可能是使用OPENJSON函数。
OPENJSON中有关于这种方法的博客——按id列表选择行的最佳方法之一。
下面是一个完整的示例
CREATE TABLE dbo.Tags
(
Name VARCHAR(50),
Count INT
)
INSERT INTO dbo.Tags
VALUES ('VB',982), ('ruby',1306), ('rails',1478), ('scruffy',1), ('C#',1784)
GO
CREATE PROC dbo.SomeProc
@Tags VARCHAR(MAX)
AS
SELECT T.*
FROM dbo.Tags T
WHERE T.Name IN (SELECT J.Value COLLATE Latin1_General_CI_AS
FROM OPENJSON(CONCAT('[', @Tags, ']')) J)
ORDER BY T.Count DESC
GO
EXEC dbo.SomeProc @Tags = '"ruby","rails","scruffy","rubyonrails"'
DROP TABLE dbo.Tags
create FUNCTION [dbo].[ConvertStringToList]
(@str VARCHAR (MAX), @delimeter CHAR (1))
RETURNS
@result TABLE (
[ID] INT NULL)
AS
BEG
IN
DECLARE @x XML
SET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'
INSERT INTO @result
SELECT DISTINCT x.i.value('.', 'int') AS token
FROM @x.nodes('//t') x(i)
ORDER BY 1
RETURN
END
——你的查询
select * from table where id in ([dbo].[ConvertStringToList(YOUR comma separated string ,',')])
唯一的制胜方法就是不玩。
对你来说没有无限的可变性。只有有限的可变性。
在SQL中,你有这样一个子句:
and ( {1}==0 or b.CompanyId in ({2},{3},{4},{5},{6}) )
在c#代码中,你可以这样做:
int origCount = idList.Count;
if (origCount > 5) {
throw new Exception("You may only specify up to five originators to filter on.");
}
while (idList.Count < 5) { idList.Add(-1); } // -1 is an impossible value
return ExecuteQuery<PublishDate>(getValuesInListSQL,
origCount,
idList[0], idList[1], idList[2], idList[3], idList[4]);
基本上,如果count为0,那么就没有过滤器,所有东西都会通过。如果计数高于0,则该值必须在列表中,但列表已被填充为5个不可能的值(因此SQL仍然有意义)
有时蹩脚的解决方案是唯一真正有效的。
有一个很好的、简单的、经过测试的方法:
/* Create table-value string: */
CREATE TYPE [String_List] AS TABLE ([Your_String_Element] varchar(max) PRIMARY KEY);
GO
/* Create procedure which takes this table as parameter: */
CREATE PROCEDURE [dbo].[usp_ListCheck]
@String_List_In [String_List] READONLY
AS
SELECT a.*
FROM [dbo].[Tags] a
JOIN @String_List_In b ON a.[Name] = b.[Your_String_Element];
我已经开始使用这种方法来修复实体框架的问题(对于我们的应用程序来说不够健壮)。所以我们决定给Dapper(和Stack一样)一个机会。还指定您的字符串列表为表与PK列修复您的执行计划很多。 这是一篇关于如何将一个表转换为Dapper的好文章——都是快速而干净的。