小编典典

什么是更好的动态SQL或在什么情况下使用?

sql

我需要创建一个使用12个参数的存储过程,并使用此参数的不同组合来过滤查询。所有12个参数都不是强制性的,好像我传递3或5或12个参数取决于用户输入的搜索输入。

我可以使用动态SQL查询或“ Case where”语句创建2种方法。这些查询的示例如下:

  1. 动态查询

    DECLARE @sql VARCHAR(MAX)
    

    DECLARE @condition VARCHAR(MAX)=’‘
    Declare @var1 varchar(10)
    Declare @var2 varchar(10)
    Declare @var3 varchar(10)
    SET @sql=’SELECT * FROM TableDemo1 TD1 WITH(NOLOCK)
    INNER JOIN TableDemo2 TD2 ON TD1.Column1=TD2.Column2’
    if(@var1 <>0 and @var1 is not null)
    begin
    if(@condition<>’‘)
    begin
    set @condition=@condition + ‘ and TD1.columnTest1=’+@var1)
    end
    else
    begin
    set @condition=’ where TD1.columnTest1=’+@var1
    end
    end
    if(@var2 <>0 and @var2 is not null)
    begin
    if(@condition<>’‘)
    begin
    set @condition=@condition + ‘ and TD2.columnTest2=’+@var2)
    end
    else
    begin
    set @condition=’ where TD2.columnTest2=’+@var2
    end
    end

    if(@var3 <>0 and @var3 is not null)
    begin
    if(@condition<>’‘)
    begin
    set @condition=@condition + ‘ and TD1.columnTest3=’+@var3)
    end
    else
    begin
    set @condition=’ where TD1.columnTest3=’+@var3
    end
    end
    SET @sql=@sql+@condition
    EXEC(@sql)

  2. 用CASE WHERE查询

    Declare @var1 varchar(10)
    

    Declare @var2 varchar(10)
    Declare @var3 varchar(10)
    SELECT * FROM TableDemo1 TD1 WITH(NOLOCK)
    INNER JOIN TableDemo2 TD2 ON TD1.Column1=TD2.Column2
    WHERE
    (CASE WHEN
    (@var1<>0 and @var1 is not null)
    THEN
    CASE WHEN TD1.columnTest1=@var1 THEN 1 ELSE 0 END
    ELSE 1 END)=1
    AND
    (CASE WHEN
    (@var2<>0 and @var2 is not null)
    THEN
    CASE WHEN TD2.columnTest2=@var2 THEN 1 ELSE 0 END
    ELSE 1 END)=1
    AND
    (CASE WHEN
    (@var3<>0 AND @var3 IS NOT NULL)
    THEN
    CASE WHEN TD1.columnTest3 = @var3
    THEN 1 ELSE 0 END
    ELSE 1 END) =1

这只是我的存储过程的一部分,有7-8个带有联接的表,并且在各种情况下如上所述。

如果使用动态查询,则SQL Server每次都必须创建一个执行计划,但是如果使用“ case where”,这也会使查询变慢。

我知道动态SQL的缺点,但是应该使用哪种技术?


阅读 41

收藏
2021-04-28

共1个答案

小编典典

通常情况下,这取决于条件,但是大多数情况下,我将动态查询作为最后的选择。关于您的问题,我很可能会使用CASE解决方案,但是我认为您的CASE表达式不必要地复杂。我将用以下内容替换WHERE子句:

...
WHERE
    TD1.columnTest1 = COALESCE(NULLIF(@var1, 0), TD1.columnTest1)
    AND   
    TD2.columnTest2 = COALESCE(NULLIF(@var2, 0), TD2.columnTest2)
    AND   
    TD1.columnTest3 = COALESCE(NULLIF(@var3, 0), TD1.columnTest3)

有了正确的索引,这应该不会太慢。

2021-04-28