条件连接 - 动态 SQL

Question

工作中的 DBA 试图将我简单的存储过程变成一个动态的 sql 怪物。 诚然，我的存储过程可能没有他们希望的那么快，但我忍不住相信有一种足够的方法来完成基本上是条件连接的操作。

下面是我的存储过程的示例：

SELECT 
*
FROM
table
WHERE
(
    @Filter IS NULL OR table.FilterField IN 
    (SELECT Value FROM dbo.udfGetTableFromStringList(@Filter, ','))
)

UDF 将逗号分隔的过滤器列表（例如，银行名称）转换为表。

显然，在 where 子句中包含过滤条件并不理想。 欢迎提出基于存储过程参数有条件加入的更好方法的建议。 除此之外，是否有人对动态 sql 方法有任何支持或反对的建议？

谢谢

Answer 1

您可以对从 UDF 返回的表进行 INNER JOIN，而不是在 IN 子句中使用它。

您的 UDF 可能类似于

CREATE FUNCTION [dbo].[csl_to_table] (@list varchar(8000) )
RETURNS @list_table TABLE ([id] INT)
AS
BEGIN
    DECLARE     @index INT,
            @start_index INT,
            @id INT

    SELECT @index = 1 
    SELECT @start_index = 1
    WHILE @index <= DATALENGTH(@list)
    BEGIN

        IF SUBSTRING(@list,@index,1) = ','
        BEGIN

            SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT)
            INSERT @list_table ([id]) VALUES (@id)
            SELECT @start_index = @index + 1
        END
        SELECT @index  = @index + 1
    END
    SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT)
    INSERT @list_table ([id]) VALUES (@id)
    RETURN
END

然后对返回表中的 id 进行 INNER JOIN。 此 UDF 假设您在逗号分隔列表中传递 INT

编辑：

为了处理为 @filter 传递的空值或无值，我能看到的最直接的方法是根据 @filter 值在存储过程中执行不同的查询。 我不确定这如何影响缓存的执行计划（如果有人可以确认，则会更新）或者最终结果是否比原始存储过程更快，我认为这里的答案在于测试。

Answer 2

看起来另一个答案正在解决代码重写问题，但反对存储过程中动态 SQL 的一个很好的论据是它破坏了所有权链。

也就是说，当您正常调用存储过程时，它会在存储过程所有者的权限下执行，除了使用execute命令执行动态SQL时，对于动态SQL的上下文，它会恢复到调用者的权限，这可能根据您的安全模型，这是不需要的。

最后，您可能最好妥协并重写它来解决 DBA 的担忧，同时避免动态 SQL。

Answer 3

我不确定我是否理解您对动态 SQL 的厌恶。 也许您的 UDF 已经很好地抽象出了问题的一些混乱，并且您觉得动态 SQL 会将其带回来。 好吧，考虑到大多数（如果不是全部）DAL 或 ORM 工具将广泛依赖于动态 SQL，我认为您的问题可以重述为“如何很好地抽象出动态 SQL 的混乱”。

就我而言，动态 SQL 准确地为我提供了我想要的查询，以及随后我正在寻找的性能和行为。

Answer 4

我不认为你的方法有什么问题。 老实说，重写它以使用动态 SQL 根据 @Filter 是否为 null 来执行两个不同的查询似乎很愚蠢。

我能看到的唯一潜在的缺点是，它可能会导致确定良好的执行计划时遇到一些困难。 但如果性能本身就足够好，就没有理由改变它。

Answer 5

无论你做什么（这里的答案都有优点），一定要比较每个选项的性能和执行计划。

有时，如果手动优化会影响代码的可维护性并且实际上不会对代码的执行方式产生任何影响，那么它就毫无意义。

我首先简单地看看将 IN 更改为带有 NULL 检查的简单 LEFT JOIN （这不会消除您的 udf，但是它应该只被调用一次）：

SELECT *
FROM table
LEFT JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter
    ON table.FilterField = filter.Value
WHERE @Filter IS NULL
    OR filter.Value IS NOT NULL

Answer 6

看来您正在尝试编写一个查询来处理两种情况：
1. @filter = "x,y,z"
2. @filter IS NULL

为了优化场景 2，我会在 UDF 上进行 INNER JOIN，而不是使用 IN 子句...

SELECT * FROM table
INNER JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter
  ON table.FilterField = filter.Value

为了优化场景 2，我不会尝试调整现有查询，而是会故意保留这些查询情况分开，无论是 IF 语句还是 UNION 并使用 WHERE 子句模拟 IF...

TSQL IF

IF (@filter IS NULL)
  SELECT * FROM table
ELSE
  SELECT * FROM table
  INNER JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter
    ON table.FilterField = filter.Value

UNION来模拟IF

SELECT * FROM table
  INNER JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter
    ON table.FilterField = filter.Value

UNION ALL

SELECT * FROM table WHERE @filter IS NULL

这样设计的优点是每种情况都很简单，而判断哪个简单本身就很简单。 然而，将两者组合成一个查询会导致诸如 LEFT JOIN 之类的妥协，从而给每个查询带来显着的性能损失。