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)

possible duplicate of [SQL Server SP - Pass parameter for "IN" array list?](http://stackoverflow.com/questions/537087/sql-server-sp-pass-parameter-for-in-array-list) — Lamak, Apr 26 '12 at 12:27
Have you considered using a [Table-Value Parameter](http://msdn.microsoft.com/en-us/library/bb510489.aspx) instead. It would make it `select Locations from table where Where LocationID in (Select Location from @LocationList)` — Conrad Frix, Apr 26 '12 at 21:32

SQLMason · Accepted Answer · 2012-04-26T13:22:36.683

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.

Please note, you **may** need to cast as needed – SQLMason Apr 26 '12 at 12:43 — SQLMason, Apr 26 '12 at 12:43

Simon · Answer 2 · 2014-06-12T15:54:58.413

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 + '|%')

score 0 · Answer 3 · edited Apr 27 '12 at 17:49

0

declare @querytext Nvarchar(MAX)

set @querytext = 'select Locations from table where Where LocationID in (' + @LocationList + ');';

exec sp_executesql @querytext;

edited Apr 27 '12 at 17:49

Conrad Frix

51,984
12
96
155

answered Apr 26 '12 at 12:30

rt2800

3,045
2
19
26

Highly suggest against this approach, too much risk for sql injection. In general stay away from the exec method, it's too dangerous. – Michael Allen Apr 26 '12 at 12:53
@MichaelAllen It depends on how your list is generated, however you're right about SQL injection and **sp_executesql** would be better - as it can even cache the execution plan. – SQLMason Apr 26 '12 at 13:01

SQL in (@Variable) query

3 Answers3

Linked

Related