Dynamic Where clause

Hello everyone,

I want to build a dynamic where clause which makes :

WHERE column1 = (@.parameter1 if @.parameter1 is not null) / (anything if @.parameter1 is null)

Basically I do not know how to set column1 = ANYTHING

Best regards and thanks.

Maybe something like

Code Snippet

Where @.parmameter1 is null

or @.parameter1 is not null and column1 = @.parameter1

Another alternative would be to use IF / ELSE around two distinct select statements rather than this particular WHERE clause. I suspect that this where syntax (and also the COALESCE syntax) will precipitate a SCAN instead of a seek. If your table is very small this won't matter.

You might be able to avoid the SCAN when you pass the parameter by using the IF / ELSE syntax PROVIDED that you have an index on column1.

|||

You can also use the coalesce function to determine which value to use. Here is an example using sys.databases

Code Snippet

DECLARE @.param1 NVARCHAR(20)

SET @.param1 = 'master'

-- SET @.param1 = NULL

SELECT * FROM sys.databases

WHERE name = COALESCE(@.param1, name)

Try it with both parameter settings. One will return just the row for master, the other will return all rows.

|||@. Kent Waldrop Thank you very much for this trick.. It seems simple but does a lot !