How to return all rows if IN clause has no value?

in-clause, performance, sql-like, sql-server-2005, t-sql

Solution

You'll need dynamic SQL or a split function for this anyway, since `IN ('1,2,3')` is not the same as `IN (1,2,3)`.

Split function:

CREATE FUNCTION dbo.SplitInts
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)
RETURNS TABLE
AS
   RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( 
     SELECT Item = x.i.value('(./text())[1]', 'int') FROM ( 
       SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') 
       + '</i>').query('.') ) AS a CROSS APPLY [XML].nodes('i') AS x(i)) AS y
     WHERE Item IS NOT NULL
   );

Code becomes something like:

SELECT m.col1, m.col2 FROM dbo.model AS m
LEFT OUTER JOIN dbo.SplitInts(NULLIF(@brandids, ''), ',') AS br
ON m.brandid = COALESCE(br.Item, m.brandid)
LEFT OUTER JOIN dbo.SplitInts(NULLIF(@bodystyleid, ''), ',') AS bs
ON m.bodystyleid = COALESCE(bs.Item, m.bodystyleid)
WHERE (NULLIF(@brandids, '') IS NULL OR br.Item IS NOT NULL)
AND (NULLIF(@bodystyleid, '') IS NULL OR bs.Item IS NOT NULL);

(Note that I added a lot of NULLIF handling here... if these parameters don't have a value, you should be passing NULL, not "blank".)

Dynamic SQL, which will have much less chance of leading to bad plans due to parameter sniffing, would be:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT columns FROM dbo.model
WHERE 1 = 1 '
+ COALESCE(' AND brandid IN (' + @brandids + ')', '')
+ COALESCE(' AND bodystyleid IN (' + @bodystyleid + ')', '');

EXEC sp_executesql @sql;

Of course as @JamieCee points out, dynamic SQL could be vulnerable to injection, as you'll discover if you search for dynamic SQL anywhere. So if you don't trust your input, you'll want to guard against potential injection attacks. Just like you would if you were assembling ad hoc SQL inside your application code.

When you move to SQL Server 2008 or better, you should look at table-valued parameters (example here).

Problem

Following is sample query. ``` CREATE PROCEDURE GetModel ( @brandids varchar(100), -- brandid="1,2,3" @bodystyleid varchar(100) -- bodystyleid="1,2,3" ) AS select * from model where brandid in (@brandids) -- use a UDF to return table for comma delimited string and bodystyleid in (@bodystyleid) ``` My requirement is that if `@brandids` or `@bodystyleid` is blank, query should return all rows for that condition. Please guide me how to do this? Also suggest how to write this query to optimize performance.

Original source