CODEWITHSUNDEEP
sqlsql-server-2005sql-server-2008t-sql
AJM
AJM

Reputation: 32490

Alternative to NULL check and then OR for optional paramaters

I use this pattern for optional filter paramaters in my SQL Stored Procedures

            AND  (
                @OptionalParam IS NULL OR
                (
                    Id = @OptionalParam
                )
            )

However the OR is not a friend of the query optimizer. Is there a more efficient way to do this without using dynamic SQL

Upvotes: 1

Views: 1643

Answers (3)

JackAce
JackAce

Reputation: 1405

AND ID = ISNULL(@OptionalParam, ID)

or you if you had multiple optional parameters can use

AND ID = COALESCE(@OptionalParam1, @OptionalParam2, ID)

This is definitely faster than using an OR statement.

Like the other answerer mentioned, this will not work if the ID column is null (but then again, the original statement wouldn't either).

Upvotes: 1

mwigdahl
mwigdahl

Reputation: 16578

You could try:

AND Id = CASE WHEN @OptionalParam IS NULL THEN Id ELSE NULL END

I doubt this will optimize much better, but there's no OR in it.

Alternatively, you could break your query apart into two components -- one with just an @OptionalParam IS NULL test and another with an Id = @OptionalParam test, then UNION them together. Depending on your data topology this might yield better results. It could also be significantly worse.

Upvotes: 0

jmacinnes
jmacinnes

Reputation: 1609

You can try using COALESCE. Not sure if it will be more efficient.

AND Id = Coalesce(@OptionalParam, Id)

This will not work if Id itself is null and you are using ANSI nulls.

Upvotes: 1

Related Questions