SQL in (@Variable) query

Question

I have the following code, the problem is that my variable list @LocationList is essentially a csv string. When I use this as part of the where LocationID in (@LocationList) it says its not an int (LocationID is an int). How can I get this csv string to be accepted by teh in clause? ``` Declare @LocationList varchar(1000) Set @LocationList = '1,32' select Locations from table where Where LocationID in (@LocationList) ```

Accepted Answer

The most efficient way to do this is with Dynamic SQL such as rt2800 mentions (with injection warnings by Michael Allen)

However you can make a function:

```
ALTER  FUNCTION [dbo].[CSVStringsToTable_fn] ( @array VARCHAR(8000) )
RETURNS @Table TABLE ( value VARCHAR(100) )
AS 
    BEGIN
        DECLARE @separator_position INTEGER,
            @array_value VARCHAR(8000)

SET @array = @array + ','

WHILE PATINDEX('%,%', @array) <> 0 
            BEGIN
                SELECT  @separator_position = PATINDEX('%,%', @array)
                SELECT  @array_value = LEFT(@array, @separator_position - 1)

INSERT  @Table
                VALUES  ( @array_value )

SELECT  @array = STUFF(@array, 1, @separator_position, '')
            END
        RETURN
    END
```

and select from it:

```
DECLARE @LocationList VARCHAR(1000)
SET @LocationList = '1,32'

SELECT  Locations 
FROM    table
WHERE   LocationID IN ( SELECT   *
                           FROM     dbo.CSVStringsToTable_fn(@LocationList) )
```

OR

```
SELECT  Locations
FROM    table loc
        INNER JOIN dbo.CSVStringsToTable_fn(@LocationList) list
            ON list.value = loc.LocationID
```

Which is extremely helpful when you attempt to send a multi-value list from SSRS to a PROC.

SQL in (@Variable) query

Solution

Problem

Related problems