Is it possible to use CASE with IN?
I'm trying to construct a T-SQL statement with a WHERE
clause determined by an input parameter. Something like:
SELECT * FROM table
WHERE id IN
CASE WHEN @param THEN
(1,2,4,5,8)
ELSE
(9,7,3)
END
I'v开发者_如何转开发e tried all combination of moving the IN, CASE etc around that I can think of. Is this (or something like it) possible?
try this:
SELECT * FROM table
WHERE (@param='??' AND id IN (1,2,4,5,8))
OR (@param!='??' AND id in (9,7,3))
this will have a problem using an index.
The key with a dynamic search conditions is to make sure an index is used, instead of how can I easily reuse code, eliminate duplications in a query, or try to do everything with the same query. Here is a very comprehensive article on how to handle this topic:
Dynamic Search Conditions in T-SQL by Erland Sommarskog
It covers all the issues and methods of trying to write queries with multiple optional search conditions. This main thing you need to be concerned with is not the duplication of code, but the use of an index. If your query fails to use an index, it will preform poorly. There are several techniques that can be used, which may or may not allow an index to be used.
here is the table of contents:
Introduction The Case Study: Searching Orders The Northgale Database Dynamic SQL Introduction Using sp_executesql Using the CLR Using EXEC() When Caching Is Not Really What You Want Static SQL Introduction x = @x OR @x IS NULL Using IF statements Umachandar's Bag of Tricks Using Temp Tables x = @x AND @x IS NOT NULL Handling Complex Conditions Hybrid Solutions – Using both Static and Dynamic SQL Using Views Using Inline Table Functions Conclusion Feedback and Acknowledgements Revision History
if you are on the proper version of SQL Server 2008, there is an additional technique that can be used, see: Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later)
If you are on that proper release of SQL Server 2008, you can just add OPTION (RECOMPILE)
to the query and the local variable's value at run time is used for the optimizations.
Consider this, OPTION (RECOMPILE)
will take this code (where no index can be used with this mess of OR
s):
WHERE
(@search1 IS NULL or Column1=@Search1)
AND (@search2 IS NULL or Column2=@Search2)
AND (@search3 IS NULL or Column3=@Search3)
and optimize it at run time to be (provided that only @Search2 was passed in with a value):
WHERE
Column2=@Search2
and an index can be used (if you have one defined on Column2)
if @param = 'whatever'
select * from tbl where id in (1,2,4,5,8)
else
select * from tbl where id in (9,7,3)
精彩评论