How to use 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 it (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, an example is provided.

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

@locid are user-defined results, e.g. '33, 234 '

If @lid is empty, I would like it to print all lines for location_id with name and application_id. Otherwise, I would like it to output all the lines with respect to the numbers provided 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

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

amuses

Answer 2

If @locid is a list, for example, "33, 234", etc., then no solution will work here. However, I think they were published before your update with this information.

I assume that since you said this:

@locid - results received by the user, for example '33, 234 '

You cannot directly expand a variable to location_in IN (33, 234) . You are really asking for location_id = '33, 234' , which will not be executed with CAST conversion due to data type priority.

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

Answer 3

See this blog post:

• Comma Separated IN: SQL Server

If your @lid is a comma-separated 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 4

Assuming that @locid and @lid are the same , I will use @locid ... The following will work. I split it up so that it looks good on SO.

 SELECT application_id, [name], location_id FROM apps WHERE ( @locid = 0 OR CHARINDEX ( ',' + CAST(location_id AS VARCHAR(50)) + ',' , ',' + @locid + ',' , 0 ) > 0 ) 

Answer 5

 WHERE CHARINDEX(LocationId, @lid) > 1 

Answer 6

One way is to split the string into a comma, cross out the spaces, and paste the values ​​into the temporary table. Then you can join your query in temp table.

This is a piece of T-SQL code that breaks a comma-separated list and inserts members into a temporary table. Once you have completed the table, you can join 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 

Answer 7

For this you do not need a triple operator:

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

Answer 8

Here you ask different questions. This is the answer to your initial question (about the ternary operator, but, as you can see, you do not need anything like a triple operator):

 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 is varchar and may contain different values, separated by commas.

Well, this (about CSV) is another question that was asked here before:

Passing "to" the list through a stored procedure
T-SQL stored procedure that takes multiple Id values

Answer 9

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 specific value, only a string is returned for this @lid value.

Answer 10

While using 1 = 1 is not the best, this does the trick.

 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)