SQL Server 2005: Select statement in ISNULL state

Question

Environment: SQL Server 2005

I have a saved proc that gets a comma-separated value in one parameter. I wrote the table value of the UDF function in SQL to split it and return it to me as a table. I use this value to filter data in the where clause of a stored procedure. Everything works fine until there is NULL in this variable, separated by a comma, passed to the stored proc. I want to write a where clause in such a way that if the passed variable is NULL, it should not use the Split function. Here is what I do in saved 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 a condition to select Col3, how is Col2 selected?

Answer 1

If the idea is to not filter by Col3 , if @list is null, you can 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 that would be nice if the tables were very small. For plan caching purposes, it is usually best to split all these permutations into your own SQL queries, rather than trying to write one size for the entire query or (if the number of permutations becomes too large), consider using dynamic SQL (see Dynamic search conditions in T -SQL )

Note. This does not guarantee that the Split function will not be called. There is no guaranteed bid evaluation procedure. But this means that if it is called, it will not affect the result of the request. ( Change , assuming, of course, what is called when NULL does not actually cause any cf Remus comment error)

Answer 2

You have a query with two variables that are independent of each other. Including both of them in one query causes a query that cannot be derogated - the query is very different depending on the values ​​of the variables, and you use expensive conditional expressions to support them together. This is a pain to maintain, and poor performance.

You can use the 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 is still zero treatment. For two variables, these are four possible outcomes. Situations like these are what is intended for dynamic SQL - to build the necessary query, because luggage does not work well.

Answer 3

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