using dynamic criteria in SQL query [duplicate]

Question

If I have a query that runs a search with optional parameters, my usual approach is to pass in NULL for any unused criteria, and have a WHERE clause that looks like

WHERE (@Param IS NULL OR Field=@Param) AND (@Param2 IS NULL OR Field2=@Param2)

If I have a condition that maybe is a little more complex to evaluate, say a LIKE clause, is SQL Server going to evaluate the IS NULL and then short circuit the more complex condition? Is there something I could look at in the execution plan to find out if it is or not? Is this behavior dependent on the plan chosen by the optimizer, such that the answer might be different in my development and production environments (I've got limited access to see what's going on in production, so I'm hoping behaviors would be consistent).

What version are you on? This is always a bad idea on anything except small tables unless you have OPTION RECOMPILE and the behaviour of that varies. — Martin Smith
– Martin Smith, Commented Mar 3, 2011 at 22:57
Are you asking if (@Param IS NULL OR Field LIKE @Param) will short circuit the LIKE check? Yes it will. — Bryan S.
– Bryan S., Commented Mar 3, 2011 at 22:58
@Bryan - But it won't generally speaking be as efficient as the cached plan needs to work for all possible parameter values. It will cause a scan rather than a seek when the @param is not null. Actually LIKE will be less effected than = though as the plan for that will just have a range seek. — Martin Smith
– Martin Smith, Commented Mar 3, 2011 at 23:02
@Martin - Yes I agree it won't be, I was just seeking clarification on what's being asked. — Bryan S.
– Bryan S., Commented Mar 3, 2011 at 23:06
See this answer to a similar question on parameter based queries. In his answer he links to an excellent article: Dynamic Search Conditions in T-SQL by Erland Sommarskog — Tony
– Tony, Commented Mar 3, 2011 at 23:48

Ryk · Accepted Answer · 2011-03-04 02:20:32Z

0

I use CASE Statements.

So yours is like this:

WHERE (@Param IS NULL OR Field=@Param) AND (@Param2 IS NULL OR Field2=@Param2)

And I will write it like this:

WHERE CASE WHEN @Param IS NULL THEN 1 ELSE Field END = CASE WHEN @Param IS NULL THEN 1 ELSE @Param END AND  
CASE WHEN @Param2 IS NULL THEN 1 ELSE Field2 END = CASE WHEN @Param2 IS NULL THEN 1 ELSE @Param2 END

Have a look at the query execution plans you get too, in a lot of cases when you use the OR condition the Query Optimizer uses a table scan. You can also replace the '=' with 'like' etc, and in fact you can even wrap that up in a case statement too. So you can replace the '=' with CASE WHEN @Param3 = 'eq' THEN = ELSE LIKE END

Hope this helps.

answered Mar 4, 2011 at 2:20

Ryk

3,1025 gold badges30 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andriy M Over a year ago

Isn't CASE WHEN @Param IS NULL THEN 1 ELSE @Param END effectively the same as COALESCE(@Param, 1)? It would be great to simplify the left-side expressions as well, but at least you could do that with the right-side ones.

Ryk Over a year ago

Yes/No, logically yes, but is some cases, depending on parameters passed in, different QEP's.

Sybe · Accepted Answer · 2019-01-26 22:07:10Z

I found a more practical solution that does avoid the use of dynamic SQL.

Suppose you have a bit field and the parameter is 1, 0 or (null = both). Then use the following SQL:

WHERE a.FIELD=CASE WHEN ISNULL(@PARAM,-1)=-1 THEN a.FIELD ELSE @PARAM END

With other words: if you dont provide an explicit value for @param then tell that A.field = a.field which is always true. In case of nullable fields you need to apply an ISNULL on that field to avoid that null=null does not come through.

Example: on INT field that can be null,

AND ISNULL(a.CALL_GENERAL_REQUIREMENTS,-1)=CASE WHEN ISNULL(@CALL_GENERAL_REQUIREMENTS,-2)=-2 THEN ISNULL(a.CALL_GENERAL_REQUIREMENTS,-1) ELSE @CALL_GENERAL_REQUIREMENTS END

As you can see I apply -2 to the null value of the param @CALL_GENERAL_REQUIREMENTS this is because real values to select on can be 0 (not passed), 1 (passed), -1 (not evaluated yet). So a -2 means do not select on this field

Example on nullable string field:

AND ISNULL(a.CALL_RESULT,'')=CASE WHEN ISNULL(@CALL_RESULT,'')='' THEN ISNULL(a.CALL_RESULT,'') ELSE @CALL_RESULT END

All works as a charm and avoids a lot of hassle on creating a dynamic SQL string with the concatenation and does not require specific permissions to be assigned to be able to run an exec statement.

Hope this helps anyone as it helped me.

Have nice day

Collectives™ on Stack Overflow

using dynamic criteria in SQL query [duplicate]

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related