条件连接-动态SQL

Question

这里的DBA正在尝试将我简单的存储过程转变为一个动态的sql怪物。诚然，我的存储过程可能没有它们想要的那么快，但我不禁相信有一种足够的方法来执行基本上是条件连接的操作。

下面是我的存储进程的一个示例：

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

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

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

谢谢

Answer 1

您可以对从UDF返回的表进行内部连接，而不是在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

然后对返回表中的ids进行内部联接。此UDF假设您在逗号分隔的列表中传入INTs

编辑：

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

Answer 2

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

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

最后，您最好折衷并重写它，以解决DBA所关心的问题，同时避免使用动态SQL。

Answer 3

我不确定我是否理解您对动态SQL的反感。也许是因为您的UDF很好地抽象了问题的一些杂乱无章，并且您觉得动态SQL将把它带回来。考虑到大多数(如果不是全部) DAL或ORM工具将广泛依赖于动态SQL，我认为您的问题可以被重申为“我如何才能很好地抽象出动态SQL的混乱”。

对我来说，动态SQL提供了我想要的查询，以及随后我想要的性能和行为。