sql - 存储过程中的条件 WHERE 子句

Question

这个问题可能归结为更简单的问题，但我仍然很好奇 SQL Server/TSQL 可以接近条件 WHERE条款(以及为什么它们不存在背后的推理也很有趣)。

我有一个存储过程，对于一些参数，它接收一个枚举数组(它已相应地转换为一个用户定义的表类型，它基本上模拟了一个 int 数组)。引用数据类型如下:

CREATE TYPE myIntArray AS TABLE (
    val INT
);

我的存储过程如下(改为更简单):

CREATE PROCEDURE myProc
    @homeID INT,
    @name VARCHAR(500),
    @hometype_enum myIntArray READONLY,
    @country_enum myIntArray READONLY
AS
BEGIN
    SELECT * FROM my_table
    WHERE name=@name
END
GO

我想要做的是根据作为 INT 表传入的枚举数组的值额外过滤我的查询结果，IFF 他们甚至有传入的值(表可能是空的)。伪代码如下所示:

SELECT * 
FROM my_table
WHERE name = @name
IF((SELECT COUNT(val) FROM @hometype_enum) > 0)
BEGIN
    AND hometype IN (SELECT val FROM hometype_enum)
END
IF((SELECT COUNT(val) FROM @country_enum ) > 0)
BEGIN
    AND country IN (SELECT val FROM country_enum )
END

这两个枚举是相互独立的，因此可以在没有枚举(两个表都为空)、非此即彼或两个枚举上进行搜索和过滤。

我的实际查询涉及多个列、表和联合(丑陋，我知道)，所以它不如能够复制/粘贴 3 行 SELECT 好对于每个场景。我目前正在使用一些非常丑陋的临时表逻辑，我现在会保护读者的眼睛。

除了找出我的特定问题之外，我的主要问题是:SQL Server 是否支持条件 WHERE条款陈述(我确信这不是来自我的研究)？为什么会这样(架构、时间复杂度、空间复杂度问题)？是否有更多或更少的简洁方法来模拟条件子句，例如 taking advantage of conditional short-circuiting ?

谢谢大家的见解。又是学习的一天!

Answer 1

正如评论中所建议的，处理这种条件 where 子句的最佳方法是使用 dynamic-sql ..... 类似......

CREATE PROCEDURE myProc
    @homeID INT,
    @name VARCHAR(500),
    @hometype_enum myIntArray READONLY,
    @country_enum myIntArray READONLY
AS
BEGIN
 SET NOCOUNT ON

 Declare @Sql NVarchar(MAX);

 SET @Sql = N' SELECT * FROM my_table '
          + N' WHERE name = @name '
           + CASE WHEN EXISTS (Select * FROM @hometype_enum)
             THEN N' AND hometype IN (SELECT val FROM hometype_enum) ' ELSE N' ' END
           + CASE WHEN EXISTS (Select * FROM @country_enum)
             THEN N' AND country IN (SELECT val FROM country_enum ) ' ELSE N' ' END

  Exec sp_executesql @Sql
                    ,N'@homeID INT , @name VARCHAR(500),
                      @hometype_enum myIntArray, @country_enum myIntArray'
                    ,@homeID
                    ,@name
                    ,@hometype_enum
                    ,@country_enum

END
GO

使用 sp_executesql将允许 sql server 存储相同存储过程的参数化执行计划。对于同一存储过程的不同参数集/组合，它是不同的执行计划，以获得最佳性能。