0

I have a C# app that passes in the following data:

datasetID = 10; userID = 1; varnames = "'ACT97','ACTCHNG','ACTQTR2','ACTSCOR2'";

The stored procedure is:

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

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

Each ACTXXX above is a varname from the column, var_name.

Why isn't it doing the IN Clause correctly?

Chris Gessler
  • 22,727
  • 7
  • 57
  • 83
cdub
  • 24,555
  • 57
  • 174
  • 303
  • 1
    This might help you: http://asheej.blogspot.com/2012/04/how-to-use-ms-sql-in-clause-with.html – Akhil Jul 06 '12 at 00:23
  • That's because no row has "'ACT97','ACTCHNG','ACTQTR2','ACTSCOR2'" string in the var_name column. You probably just have individual values there like ACT97 and ACTQTR2 and not the whole string you are checking against. Please refer here to see how the IN clause works: http://msdn.microsoft.com/en-us/library/ms177682.aspx – Andrew Savinykh Jul 06 '12 at 00:26
  • Which RDBMS? I'm asking because there's a simple way to do this using PL/SQL. – Chris Gessler Jul 06 '12 at 00:29
  • oh i need to know how to pass this in individually. I'm using MS SQL 2005 – cdub Jul 06 '12 at 00:30
  • possible duplicate of [Parameters to the EXISTS clause in a stored procedure](http://stackoverflow.com/questions/10744245/parameters-to-the-exists-clause-in-a-stored-procedure) – Aaron Bertrand Jul 06 '12 at 00:40
  • You could find many options in [this question](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause "Parameterizing an SQL IN clause?"). You might also want to have a look at questions in its *Linked* section too. – Andriy M Jul 06 '12 at 05:37

3 Answers3

1

There are several ways to accomplish this:

  1. Dynamic SQL, as pointed out in this article: http://asheej.blogspot.com/2012/04/how-to-use-ms-sql-in-clause-with.html

  2. 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)

  3. 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://dataeducation.com/faster-more-scalable-sqlclr-string-splitting/

  4. Use a Table Value Parameter (2008): http://www.techrepublic.com/blog/datacenter/passing-table-valued-parameters-in-sql-server-2008/168

  5. Here's a little trick using CHARINDEX (note that this approach is Non-Sargable):

Your string is like so: 'abc,def'

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.

Then to perform the search:

WHERE CHARINDEX ( ',' + expressionToFind + ',' , ',' + expressionToSearch ',') > 0

The delimeter padding keeps the search from finding "abcabc" but will find "abc", exact match.

If you're using 2005, I'd grab a really fast split function so you can avoid using dynamic SQL.

Community
  • 1
  • 1
Chris Gessler
  • 22,727
  • 7
  • 57
  • 83
0

Your query should use STRING_SPLIT for IN, like this:

SELECT COUNT(*) as locked FROM VarLocks 
WHERE var_name 
IN (SELECT value FROM STRING_SPLIT(@varNames, ',')) 
AND dataset_id = @datasetID 
AND user_id != @userID 
AND is_locked = 1

You had wrong syntax for IN operator: IN (@varNames) and you needed IN (SELECT value FROM STRING_SPLIT(@varNames, ',')). STRING_SPLIT performs string splitting according to delimiter, which is comma in your case.

Ivan Golović
  • 8,732
  • 3
  • 25
  • 31
-2

pass comma separated string from c#

sql WHERE Location IN (SELECT value FROM STRING_SPLIT(@SearchLocation,'|'))

khalid saif
  • 17
  • 3