SQL Server 2008 - 条件查询 [英] SQL Server 2008 - Conditional Query

问题描述

SQL 不是我的强项之一.我有一个 SQL Server 2008 数据库.这个数据库有一个接受八个 int 参数的存储过程.为了保持这个问题的重点，我将使用以下参数之一作为参考:

SQL is not one of my strong suits. I have a SQL Server 2008 database. This database has a stored procedure that takes in eight int parameters. For the sake of keeping this question focused, I will use one of these parameters for reference:

@isActive int

这些 int 参数中的每一个都是 -1、0 或 1.-1 表示未知"或不关心".基本上，我需要查询一个表，如果 int 参数不是 -1，我需要在我的 WHERE 子句中考虑它.因为有八个 int 参数，所以 IF-ELSE 语句似乎不是一个好主意.同时，我不知道还有什么办法可以做到这一点?

Each of these int parameters will be -1, 0, or 1. -1 means "Unknown" or "Don't Care". Basically, I need to query a table such that if the int parameter is NOT -1, I need to consider it in my WHERE clause. Because there are eight int parameters, an IF-ELSE statement does not seem like a good idea. At the same time, I do not know how else to do this?

如果参数不等于值，SQL 中是否有一种优雅的方法来添加 WHERE 条件?

Is there an elegant way in SQL to add a WHERE conditional if a parameter does NOT equal a value?

谢谢！

推荐答案

动态搜索条件的最佳来源:

best source for dynamic search conditions:

Erland Sommarskog 的 T-SQL 中的动态搜索条件

对于是否可以使用索引，您如何执行此操作有很多微妙的含义.如果您使用的是 SQL Server 2008 的正确版本，您只需将 OPTION (RECOMPILE) 添加到查询中，并且运行时的局部变量值用于优化.

there are a lot of subtle implications on how you do this as to if an index can be used or not. If you are on the proper release of SQL Server 2008 you can just add OPTION (RECOMPILE) to the query and the local variable's value at run time is used for the optimizations.

考虑到这一点，OPTION (RECOMPILE) 将采用此代码(其中没有索引可以用于这种乱七八糟的 OR s):

Consider this, OPTION (RECOMPILE) will take this code (where no index can be used with this mess of ORs):

WHERE
    (@search1 IS NULL or Column1=@Search1)
    AND (@search2 IS NULL or Column2=@Search2)
    AND (@search3 IS NULL or Column3=@Search3)

并在运行时对其进行优化(前提是只有@Search2 传入了一个值):

and optimize it at run time to be (provided that only @Search2 was passed in with a value):

WHERE
    Column2=@Search2

并且可以使用索引(如果您在 Column2 上定义了一个索引)

and an index can be used (if you have one defined on Column2)

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