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)

Answer 1

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.

Answer 2

I often have this requirement, and SOMETIME, if you know very well the column you are searching on [the size/format/length], you can do a kind of REGEX.

Something like this :

  DECLARE @MyListOfLocation varchar(255)
  set @MyListOfLocation  = '|1|32|36|24|3|'

  Select LocationID 
  from  Table 
  where @MyListOfLocation like '%|' +  LocationID + '|%'

NOTE : the PIPE character is used to protect the query from returning any LocationID that contains a single character (the '1', for example).

Here is a complete working example :

DECLARE @MyListOfLocation varchar(255)
set @MyListOfLocation  = '|1|11|21|'

SELECT LocationName
FROM (
        select '1' as LocationID, 'My Location 1' as LocationName
        union all
        select '11' as LocationID, 'My Location 11' as LocationName
        union all
        select '12' as LocationID, 'My Location 12' as LocationName
        union all
        select '13' as LocationID, 'My Location 13' as LocationName
        union all
        select '21' as LocationID, 'My Location 21' as LocationName
    ) as MySub
where @MyListOfLocation like '%|' + LocationID + '|%'

WARNING! This method is not Index friendly!

If you want do add some IN(@MyListOfLocation) in all that, to leverage use of INDEXES, you can modify your script do to :

SELECT MyDATA.* 
FROM   HugeTableWithAnIndexOnLocationID as MyDATA 
WHERE  LocationID in (
      Select LocationID 
      from  Table 
      where @MyListOfLocation like '%|' +  LocationID + '|%')