How do I use a comma separated list of values as a filter in T-SQL?
sql, sql-server, t-sql
Solution
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
Problem
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 ```