Dynamic where clause with if..else or case

Hello all...

I am trying rewrite an sp that I have that is considered dynamic cause it builds a where clause based on a bunch of if statements then adds it to the the end of select

i.e

if...@.where = @.where + ' llll '

if...@.where = @.where + ' llll '

select @.statement = @.statement + @.where

exec(@.statement)

I have rewritten most of it to but I have several conditions that use ' contains' for the condition and I can't get SQL server to recognize an if statement or a case statement.

Is it possible to use either statement inside a where clause?

i.e

where if a = 1 then d=e

else contains(.....)

thanks

No. You cannot use control of flow statements in the WHERE clause. CASE is an expression so you can use it in the WHERE clause. Since CONTAINS is a predicate in itself, you need to do something like:

case ... when .. then (select 1 where contains()) else ... end = 1

But it is not clear why you need to use dynamic SQL or build WHERE clause like this. Using dynamic SQL can cause performance problems and has lot of security implications. So you need to be aware of the risks while using it. CONTAINS predicate in SQL Server 2000 (from SP3 I think) and SQL Server 2005 can take a variable for the search expression so you probably don't dynamic SQL for this. If you have different checks to perform then you may actually be better off using multiple IF statements that call specific SPs with SELECT statements. This will provide the best performance advantage.

|||

Thanks. I need the dynamic where based on search criteria because it was decided to make one SP compared to about 50 or more because there are possible combinations of the criteria...

The purpose of the contains is to search a text field for a value that is close to what what asked for. This is one of our slowest SPs so I thought I would give it a shot to make it quicker but I guess I will have to just be happy with what it is.

Thanks for the help.

|||

Check out the article at the link below for various techniques on how to do dynamic searches using SPs.

http://www.sommarskog.se/dyn-search.html