主页 / dba / 问题 / 140369
Accepted
jdids
Asked: 2016-06-04 13:48:23 +0800 CST

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

  • 772

这是一个简单的，但我似乎无法弄清楚。

我有两个参数，1 和 2。如果一个参数传递了一个空值，则在 WHERE 子句中使用另一个参数。

前任：

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

我知道这很简单，或者我的逻辑可能是错误的。任何帮助或方向表示赞赏。

sql-server t-sql

3 个回答

  1. Best Answer

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

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

    SQL Server 将Field2 = @parameter2在@parameter1 IS NULL. 当“@Parameter1”不为空时，SQL 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');

    因为我知道我对该表的查询将采用的精确模式，所以我可以创建几个简单的覆盖索引，这将使 SQL 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

    在这里，我正在创建一个过程，它将使用任一内容或根据 传入的参数来搜索我们的表。Column1Column2

    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

    在不知道您的确切要求的情况下，很难说您是否需要。

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

    EXEC dbo.MySearch @parameter1 = 'col1_value';

    在此处输入图像描述

    EXEC dbo.MySearch @parameter2 = 'col2_value';

    在此处输入图像描述

    通过将存储过程与动态 SQL 结合使用，查询设计者（我们）可以通过提供两个针对每个场景进行优化的独立可缓存计划来帮助 SQL Server 始终如一地为作业选择最佳计划。根据所涉及的每列的基数、实际查询设计的复杂性以及所需索引的存在，SQL 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);

    结果是这样的计划：

    在此处输入图像描述

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

    • 9

  2. 如果我理解你，这应该做的工作：

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

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

    为了克服这个问题，您需要在每次查询运行时强制重新编译。即使更高版本的 SQL Server 具有类似于 Oracle 绑定感知的功能，拥有 2 个单独的查询也比依赖优化器魔法要容易得多。

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

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

    进一步阅读：

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

    • 6

  3. 对此不确定，但我认为它会起作用
    如果它们都有值，那么我认为它与您的案例陈述不同

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

    一片空白

    • 0

相关问题