How do I use a comma separated list of values as a filter in T-SQL?

Question

I have a basic SQL query, starting with:

SELECT top 20 application_id, [name], location_id FROM apps

Now, I would like to finish it so that it does this (written in Pseudocode)

if @lid > 0 then
    WHERE location_id IN (@lid)
else
    WHERE location_id is all values in location_id column

---

As requested, here is an example

application_id             name               location_id
----------------------------------------------------------
1                          Joe Blogs          33
2                          Sam Smith          234
3                          Jeremy Carr        33

@locid is the results given by the user, for example '33, 234'

If @lid is empty then I'd like it to output all rows for location_id with name and application_id. Otherwise, I'd like it to output all rows in relation to the provided numbers in @lid (standing for location_id.

So, if @lid is 0:

application_id             name               location_id
----------------------------------------------------------
1                          Joe Blogs          33
2                          Sam Smith          234
3                          Jeremy Carr        33

Otherwise, if @lid contains '33'

application_id             name               location_id
----------------------------------------------------------
1                          Joe Blogs          33
3                          Jeremy Carr        33

Answer 1

See this entry in my blog:

• IN with a comma separated list: SQL Server

If your @lid is a comma-delimited list of integers, use this:

WITH    cd AS
        (
        SELECT  1 AS first, CHARINDEX(',', @lid, 1) AS next
        UNION ALL
        SELECT  next + 1, CHARINDEX(',', @lid, next + 1)
        FROM    cd
        WHERE   next > 0
        ),
        lid AS
        (
        SELECT  CAST(SUBSTRING(@lid, first, CASE next WHEN 0 THEN LEN(@lid) + 1 ELSE next END - first)AS INT) AS id
        FROM    cd
        )
SELECT  d.*
FROM    (
        SELECT  DISTINCT id
        FROM    lid
        ) l
JOIN    apps a
ON      a.location_id = l.id
        AND @lid <> '0'
UNION ALL
SELECT  *
FROM    apps a
WHERE   @lid = '0'

This is much more efficient than using OR constructs.

Answer 2

Hi

Try using Case, which serves the purpose of an IIF or a ternary operator. Please check this link http://msdn.microsoft.com/en-us/library/ms181765.aspx

cheers

Answer 3

You don't really need the ternary operator for this:

SELECT top 20 application_id, [name], location_id
FROM apps
WHERE (@lid > 0 AND location_id IN (@lid)) OR @lid <= 0

Answer 4

You are asking different questions here. This is the answer to your original question (about the ternary operator, but as you can see, you don't need anything like a ternary operator for this):

SELECT top 20 application_id, [name], location_id 
FROM apps
WHERE @lid = 0 OR location_id IN (@lid)

But that was before we knew that @lid was a varchar and could contain different comma separated values.

Well, this (about the csv) is another question which has been asked here before:

http://stackoverflow.com/questions/617706/passing-an-in-list-via-stored-procedure
http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values

Answer 5

If @locid is a list eg "33, 234" etc, then no solution here will work. However, I guess these were posted before your update with this information.

I assume that because you said this:

@locid is the results given by the user, for example '33, 234'

You can not expand the variable directly so that location_in IN (33, 234). You are actually asking for location_id = '33, 234', which will fail with a CAST conversion, because of datatype precedence.

You have to parse the list first into a table form for use in a JOIN/EXISTS construct. There are several options and Erland covers them all here: Arrays and Lists in SQL Server 2005

Answer 6

The following should do the trick for you.

DECLARE @lid SMALLINT

SET @lid = 0

SELECT top 20 application_id, [name], location_id

FROM apps

WHERE ((@lid > 0 AND location_id = @lid)

  OR (@lid = 0 AND location_id > @lid))

If @lid = 0 then it will return ALL rows. IF @lid has a particular value, only the row for that @lid value is returned.

Answer 7

While it is not exactly a best practice to use 1=1, it does the trick here.

SELECT top 20 application_id, [name], location_id FROM apps
WHERE
  (@lid > 0 and @lid = location_id)
or
  (isnull(@lid, 0) <= 0 and 1=1)

Answer 8

Read this article. Regarding the IN('12, 34') thing (which won't work), read this article.

Answer 9

One way is to split the string on the commas, strip out the spaces and insert the values into a temporary table. Then you can join your query against the temp table.

This is a T-SQL code snippet that splits a comma-separated list and inserts the members into a temporary table. Once you've populated the table you can join against it.

-- This bit splits up a comma separated list of key columns
-- and inserts them in order into a table. 
--
if object_id ('tempdb..#KeyCols') is not null
    drop table #KeyCols

create table #KeyCols (
      ,KeyCol           nvarchar (100)
)

set @comma_pos = 0
set @len = len(@KeyCols)
while @len > 0 begin
    set @comma_pos = charindex(',', @KeyCols)
    if @comma_pos = 0 begin
        set @KeyCol = @KeyCols
        set @KeyCols = ''
    end else begin
        set @KeyCol = left (@KeyCols, @comma_pos - 1)
        set @KeyCols = substring(@KeyCols, 
                                 @comma_pos + 1, 
                                 len (@KeyCols) - @comma_pos)
    end
    insert #KeyCols (KeyCol)
    values (@KeyCol)
    set @len = len (@KeyCols)
end