Using IN correctly with SQL

Question

I have an application that executes a stored procedure. There is a combo box "City" and the user that can select only one value and I use that value as an SQL parameter in My C# code.

Simplified Example :

Select StudentName, Age 
From dbo.Students
Where City = @city

@city is replaced by the value from the combo box.

Now the requirement is changed and the combo box supports multiple selection. The selected elements are stored in an array. Now I am using the following approach.

1. Use String.Join("','") and join the array elements and the result is assigned to @city.

2. Changing the query to use IN operator.

   Select StudentName, Age From dbo.Students Where City IN (@city)

Suppose the selected cities are London and Sydney then @City becomes London','Sydney. Then in SP it will be used as 'London','Sydney' which is syntactically correct. But this query fails.

What do I do incorrectly? What is the best (Nice) approach to handle this?

Answer 1

The way you want to use IN is incorrect, it doesn't work that way.

Options:

1. send a CSV string to the SP then split it and create a temporary dataset (table variable for instance) with which you will then do a JOIN
2. dynamic SQL

Example split function:

CREATE FUNCTION [dbo].[Split](
    @String nvarchar (4000),
    @Delimiter nvarchar (10)
 )
RETURNS @ValueTable TABLE ([Value] nvarchar(4000), [ElementsCount] int)
BEGIN
     DECLARE @nextString nvarchar(4000)
     DECLARE @pos int
     DECLARE @nextPos int
     DECLARE @commaCheck nvarchar(1)
     DECLARE @elementsCount int

     SET @elementsCount = 0

     --Initialize
     SET @nextString = ''
     SET @commaCheck = RIGHT(@string, 1) 

     --Check for trailing Comma, if not exists, INSERT
     SET @string = @string + @delimiter

     --Get position of first Comma
     SET @pos = CHARINDEX(@delimiter, @string)
     SET @nextPos = 1

     --Loop while there is still a comma in the String of levels
     WHILE (@pos <>  0)  
     BEGIN
          SET @nextString = SUBSTRING(@string, 1, @pos - 1)

          INSERT INTO @ValueTable ([Value]) VALUES (@nextString)

          SET @string = SUBSTRING(@string, @pos +1, LEN(@string))

          SET @nextPos = @pos
          SET @pos = CHARINDEX(@delimiter, @string)

          SET @elementsCount = @elementsCount + 1
     END

     UPDATE @ValueTable SET [ElementsCount] = @elementsCount

     RETURN
END

And then you use it like so:

SELECT
    StudentName, Age
FROM
    dbo.Students
    JOIN [dbo].[Split](@city, ',') Cities ON City = Cities.Value

Answer 2

I don't know any DB which translates one bind param into multiple params as is necessary for your example to work - this would even defeat the "SQL-injection-defense" bind params provide.

You either use dynamic SQL (i.e. create the SQL statement as a string, not with a bind param - again: this is vulnerable to SQL injection!) or use one SQL query per selected value from the combobox and then create a union of the results or use some XML as the list of selected combobox values and a subquery with IN (the subquery turns the XML into rows)...

Answer 3

You can use dynamic sql for such scenario like the following:

SomeStoredProcedure(@city )

sp_executesql 'Select StudentName, Age  
From dbo.Students Where City in (' +    @city + ')'

Answer 4

Consider using xml to pass lists, like in this article. Also, you mention string.Join(). That means you pass your SQL parameters as strings. Never do that! That leads to SQL injection attacks, not what you want in production.

Answer 5

If you pass parameter to the SP and to the underlying SQL statement, you are supposed to send the well-formed string like 'London','Sydney' only.

If you are handling this as you said- "Suppose the selected cities are London and Sydney then @City becomes London','Sydney. Then in SP it will be used as 'London','Sydney' which is syntactically correct. But this query fails.", then you may update your post.

Answer 6

Since you've indicated that you're using SQL Server 2008, you might want to look into Table Valued Parameters. These let you pass a table across, with one row for each sought value.

Here's the ADO.Net documentation on the same, which includes a sample of how to pass such a parameter to a stored procedure.

Answer 7

You have 2 options.

1. 

where city in(@city1, @city2, @City3...)

2. I am assuming city is an int, which it should be.

Declare @t table(city int) 

--loop all chosen cities

insert @t values(city)
--loopend
Select s.StudentName, s.Age     
From dbo.Students s
join @t t
Where s.City = t.city