T-SQL:如果parameter1为null，则使用parameter2

Question

这是一个简单的问题，但我似乎搞不清楚。

我有两个参数，1和2。如果一个参数被传递为null值，那么在WHERE子句中使用另一个参数。

例如：

SELECT ...
 WHERE CASE
    WHEN @Parameter1 IS NULL
    THEN Field2 = @Parameter2
    WHEN @Parameter2 IS NULL
    THEN Field1 = @Parameter1
  END

我知道这很简单，或者我用错了我的逻辑。任何帮助或指导都会受到感谢。

Answer 1

对于您的问题，最简单的答案是通过在分组需求周围放置括号来告诉SQL Server要查找什么。因此，例如，如果我们只想在Field2时检查@parameter1 IS NULL，我们可以这样做：

WHERE (@parameter1 IS NULL AND Field2 = @parameter2)
    OR (Field1 = @parameter1)

Server将在Field2 = @parameter2时评估@parameter1 IS NULL。当‘@Parameter1’不是空时，Server将只返回与其匹配的行。

如果源表中有很多行，则可能需要使用存储过程来帮助优化可能的选择。下面的示例创建一个表，填充几行，并创建一个查询表的过程。

因为这只是一个例子，所以我在tempdb中这样做：

USE tempdb;

创建该表，并使用几行填充它：

IF OBJECT_ID('dbo.SomeTable') IS NOT NULL
DROP TABLE dbo.SomeTable;
GO
CREATE TABLE dbo.SomeTable
(
    SomeID INT NOT NULL
        CONSTRAINT PK_SomeTable
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Column1 NVARCHAR(30) NULL
    , Column2 NVARCHAR(30) NULL
    , Column3 NVARCHAR(200) NOT NULL
);
INSERT INTO dbo.SomeTable (Column1, Column2, Column3)
VALUES (NULL, 'col2_value', 'some data for row 1')
    , ('col1_value', NULL, 'some data for row 2');

由于我知道对这个表的查询将采用的精确模式，所以我可以创建几个简单的覆盖索引，使Server能够非常快速地找到相关的行：

CREATE INDEX IX_SomeTable_c1 ON dbo.SomeTable(Column1) INCLUDE (Column2, Column3);
CREATE INDEX IX_SomeTable_c2 ON dbo.SomeTable(Column2) INCLUDE (Column1, Column3);
GO

这将向表中添加额外的10,000行，以便为查询优化器考虑的索引提供足够的数据：

INSERT INTO dbo.SomeTable (Column1, Column2, Column3)
SELECT TOP(10000) LEFT(o1.name, 30), RIGHT(o2.name, 30), o3.name
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3

这里，我创建了一个过程，它将根据传入的参数使用Column1或Column2的内容搜索我们的表。

IF OBJECT_ID('dbo.MySearch') IS NOT NULL
DROP PROCEDURE dbo.MySearch;
GO
CREATE PROCEDURE dbo.MySearch
(
    @parameter1 NVARCHAR(30) = NULL
    , @parameter2 NVARCHAR(30) = NULL
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @where NVARCHAR(100);
    DECLARE @params NVARCHAR(100);
    SET @sql = 'SELECT t.Column1
    , t.Column2
    , t.Column3
FROM dbo.SomeTable t
';
    IF @parameter1 IS NULL
    BEGIN
        SET @params = '@p2 NVARCHAR(30)';
        SET @where = 'WHERE t.Column2 = @p2';
        SET @sql = @sql + @where;
        EXEC sys.sp_executesql @sql, @params, @p2 = @parameter2;
    END
    IF @parameter2 IS NULL
    BEGIN
        SET @params = '@p1 NVARCHAR(30)';
        SET @where = 'WHERE t.Column1 = @p1';
        SET @sql = @sql + @where;
        EXEC sys.sp_executesql @sql, @params, @p1 = @parameter1;
    END
END;
GO

如果同时传入@parameter1和@parameter2的值，则存储过程将不返回任何行。您可能需要在存储过程中添加第三个IF语句来处理这种情况，如下所示：

    IF @parameter1 IS NOT NULL AND @parameter2 IS NOT NULL
    BEGIN
        SET @params = '@p1 NVARCHAR(30), @p2 NVARCHAR(30)';
        SET @where = 'WHERE t.Column1 = @p1 AND t.Column2 = @p2';
        SET @sql = @sql + @where;
        EXEC sys.sp_executesql @sql, @params, @p1 = @parameter1, @p2 = @parameter2;
    END

如果不知道您的确切需求，很难确定您是否需要它。

两个示例显示了存储的proc的使用情况，以及执行计划：

EXEC dbo.MySearch @parameter1 = 'col1_value';

EXEC dbo.MySearch @parameter2 = 'col2_value';

通过结合使用存储过程和动态SQL，查询设计器(us)可以通过提供两个独立的可计算计划来帮助Server始终如一地选择作业的最佳计划，并对每个方案进行优化。根据所涉及的每一列的基数、实际查询设计的复杂性以及所需索引的存在，Server可能决定为这两个查询创建非常不同的计划，这可能是一件非常好的事情™。

为了好玩，我使用“简单”WHERE子句在我的答案顶部显示如下：

DECLARE @parameter1 NVARCHAR(30) = 'col1_value';
DECLARE @parameter2 NVARCHAR(30);
SELECT t.Column1
    , t.Column2
    , t.Column3
FROM dbo.SomeTable t
WHERE (@parameter1 IS NULL AND t.Column2 = @parameter2)
    OR (t.Column1 = @parameter1);

结果产生了这样的计划：

这清楚地显示了Server搜索Column1和Column2的两个索引。这显然不是最优的，当然也不是更大的数据集的首选路径。

Answer 2

如果我理解你的话，这应该是我的职责：

SELECT ...
 WHERE 
    (Field2 = @Parameter2 AND @Parameter1 IS NULL) OR 
    (Field1 = @Parameter1 AND @Parameter2 IS NULL) 

然而，由于潜在的性能问题，我宁愿不做这样的事情。此查询的执行计划在很大程度上取决于参数值。例如，将NULL作为@Parameter1传递应该使优化器倾向于在Field2上使用索引，将NULL作为@Parameter2‘-索引传递到Field1上。

要克服这一问题，每次查询运行时都需要强制重新编译。即使Server的以后版本具有类似于Oracle感知的特性，使用两个单独的查询也比依赖优化幻象容易得多。

例如，您可以修改您的过程/函数并将逻辑放在那里：

IF  @Parameter1 IS NULL 
 BEGIN
    SELECT ... WHERE Field2 = @Parameter2;
    ...
 END;
ELSE 
 BEGIN
    SELECT ... WHERE Field1 = @Parameter1;
    ...
 END;

进一步读：

T中的动态搜索条件，Erland Sommarskog

Answer 3

我不确定，但我认为它会起作用的。

如果他们都有价值，那么我认为这与你的案例陈述不同。

isnull(@Parameter1, Field2 ) = @Parameter2
or 
isnull(@Parameter2, Field1 ) = @Parameter1

isnull