SQL Server 2005: Select statement in ISNULL condition

Question

Environment: SQL Server 2005

I have a stored proc which receives comma separated value in one parameter. I have written a Table Valued UDF function in SQL to break it and and return me it as a table. I am using that value to filter the data in the where clause of the stored proc. Everything works fine until there is NULL in that comma separated variable passed to the stored proc. I want to write my where clause in such a way that if the variable passed is NULL then it should not use the Split function. Here is what i am doing in the stored proc.

Declare @list varchar(100), @Col2 varchar(100)

SELECT  *
  FROM  Table1 t1
INNER JOIN dbo.Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col2 = ISNULL(@Col2,t1.Col2)
--I want to write below condition the same way i have written the above condition
    AND t1.Col3 IN (select * from dbo.SplitMe(@list))

Is there a way to write the condition to select Col3 the way Col2 is being selected?

Answer 1

If the idea is to not filter by Col3 if @list is null then you could do it the same way

Declare @list varchar(100), @Col2 varchar(100)

SELECT  *
  FROM  Table1 t1
INNER JOIN dbo.Table2 t2
on t1.Col1 = t2.Col1

where t1.Col2 = ISNULL(@Col2,t1.Col2)
AND (@list IS NULL OR  t1.Col3 IN (select * from dbo.SplitMe(@list)))

but it would not be a good idea unless the tables are very small. For plan caching reasons it is usually best to break all these permutations into their own SQL statements rather than try and write a one size fits all query or (if the number of permutations gets too large) consider using dynamic SQL (see Dynamic Search Conditions in T-SQL)

Note this doesn't guarantee that the Split function won't get called. There is no guaranteed order of evaluation of the clauses. But it does mean that if it does get called it will have no effect on the outcome of the query. (Edit assuming of course that being called when NULL doesn't actually cause some sort of error c.f. Remus's comment)

Answer 2

You have a query with now two variables that are independent of one another. Including both of them in a single query produces a non-sargable query - the query is very different depending on the variable values and you're using expensive conditional expressions to keep it together. It's both a pain to maintain, and performs poorly.

You could use an IF statement:

IF
BEGIN

    SELECT *
       FROM TABLE1 t1
       JOIN TABLE2 t2 ON t2.col1 = t1.col1
     WHERE t1.col1 = COALESCE(@col2, t1.col2)

END
ELSE
BEGIN

    SELECT *
       FROM TABLE1 t1
       JOIN TABLE2 t2 ON t2.col1 = t1.col1
      WHERE t1.col2 = COALESCE(@col2, t1.col2)
          AND t1.col3 IN (SELECT * FROM dbo.splitme(@list))

END

...but there's still the null handling. For two variables, that's four possible value outcomes. Situations like these are what dynamic SQL is intended - to construct the query necessary because lugging baggage doesn't perform well.

Answer 3

WHERE (t1.Col2 = @Col2 OR @Col2 IS NULL)
AND (t1.Col3 IN (select * from dbo.SplitMe(@list)) OR @list IS NULL)