在SQL查询中使用动态条件 [英] using dynamic criteria in SQL query

问题描述

如果我有一个使用可选参数运行搜索的查询，我通常的方法是传入NULL以获取任何未使用的条件，并使用一个类似以下内容的WHERE子句

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)

如果我要评估的条件可能要复杂一点，例如LIKE子句，SQL Server是否要评估IS NULL然后短路更复杂的条件?我可以在执行计划中查看一些内容以找出是否存在吗?这种行为是否取决于优化程序选择的计划，因此答案在我的开发和生产环境中可能会有所不同(我只能有限地查看生产中正在发生的事情，所以我希望行为能够保持一致)

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).

推荐答案

我找到了一个更实用的解决方案，该解决方案避免了使用动态SQL.

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

假设您有一个位字段，参数为1、0或(null =两者).然后使用以下SQL:

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

在a.FIELD = CASE当ISNULL(@ PARAM，-1)=-1的情况下，然后a.FIELD ELSE @PARAM结束

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

换句话说:如果您不为@param提供显式值，则告诉A.field = a.field始终为真.对于可为空的字段，您需要在该字段上应用ISNULL，以免null = null不会通过.

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.

示例:在INT字段上可以为空，

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

如您所见，我将-2应用于参数@CALL_GENERAL_REQUIREMENTS的空值，这是因为要选择的实际值可以为0(未传递)，1(已传递)，-1(尚未评估).因此，-2表示不要在此字段上选择

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

所有功能都是一种魅力，并且避免了在创建带有串联的动态SQL字符串时的麻烦，并且不需要分配特定的权限就可以运行exec语句.

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.

祝你有美好的一天

这篇关于在SQL查询中使用动态条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！