SQL IN子句中存储过程 [英] SQL IN Clause In Stored Procedure
问题描述
我有一个通过了下列数据的C＃应用程序：
I have a C# app that passes in the following data:
datasetID所= 10;
用户ID = 1;
varnames ='ACT97'，'ACTCHNG'，'ACTQTR2'，'ACTSCOR2';
datasetID = 10; userID = 1; varnames = "'ACT97','ACTCHNG','ACTQTR2','ACTSCOR2'";
存储过程是：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[s_LockCheck]
-- Add the parameters for the stored procedure here
@varNames VARCHAR(max),
@datasetID INT,
@userID INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
SELECT COUNT(*) as locked FROM VarLocks WHERE var_name IN (@varNames) AND dataset_id = @datasetID AND user_id != @userID AND is_locked = 1
END
但是，当我这样称呼它的话，它返回0，当计数应该高于
But when I call it like so, it is returning a count of 0 when it should be higher than that:
exec s_LockCheck "'ACT97','ACTCHNG','ACTQTR2','ACTSCOR2'", 88, 14
上面的每个ACTXXX是从列VARNAME，VAR_NAME。
Each ACTXXX above is a varname from the column, var_name.
为什么不是做正确IN子句？
Why isn't it doing the IN Clause correctly?
推荐答案
有几种方法要做到这一点：
There are several ways to accomplish this:
-
动态SQL，在这篇文章中指出：的 http://asheej.blogspot.com/2012/04/how-to-使用-MS-SQL-在子句with.html
指定变量（每个项目本可以得到相当难看，如果你有一个很多'EM）的：
Specify each item in variables (this can get quite ugly if you have a lot of 'em):
@ VAR1 VARCHAR（20），
@ VAR2 VARCHAR（20），
@ VAR3 VARCHAR（20）
@var1 varchar(20), @var2 varchar(20), @var3 varchar(20)
写拆分功能，把串入表变量中，有很多人在那里。这一个是我个人最喜欢的：的 http://sqlblog.com/blogs/adam_machanic/archive/2009/04/26/faster-more-scalable-sqlclr-string-splitting.aspx
Write a split function to turn the string into a table variable, there are many of them out there. This one is my personal favorite: http://sqlblog.com/blogs/adam_machanic/archive/2009/04/26/faster-more-scalable-sqlclr-string-splitting.aspx
使用表值参数（2008）：的 http://www.techrepublic.com/blog/datacenter/passing-table-valued-parameters-in-sql-server-2008/168
Use a Table Value Parameter (2008): http://www.techrepublic.com/blog/datacenter/passing-table-valued-parameters-in-sql-server-2008/168
下面是一个使用CHARINDEX一个小窍门（请注意，这种做法是不优化搜索）：
Here's a little trick using CHARINDEX (note that this approach is Non-Sargable):
您的字符串是就像这样：'ABC，DEF
Your string is like so: 'abc,def'
使用 CHARINDEX ，你垫无论是搜索字符串和价值要与你的分隔符搜索字符串中找到。因此，使用我的小例子中，字符串将成为'，ABC，DEF，通知在开头和结尾额外逗号。然后做同样的事情到外地的数据。如果你在你的数据逗号，你就必须换出定界符别的东西，比如CHAR（2），或分号，或什么的。
Using CHARINDEX, you pad both the search string and value you want to find within the search string with your delimeter. So using my little example, the string would become ',abc,def,' Notice the extra commas at the beginning and end. Then do the same thing to the field data. If you have commas in your data, you'll have to swap out the delimeter to something else, like char(2), or semi-colons, or whatever.
然后进行搜索：
WHERE CHARINDEX ( ',' + expressionToFind + ',' , ',' + expressionToSearch ',') > 0
的分隔符填充保持搜索从发现ABCABC，但会发现ABC，精确匹配
The delimeter padding keeps the search from finding "abcabc" but will find "abc", exact match.
如果你使用2005，我抢了真快分裂功能，这样可以尽量避免使用动态SQL。
If you're using 2005, I'd grab a really fast split function so you can avoid using dynamic SQL.
这篇关于SQL IN子句中存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
