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

Question

我需要创建一个使用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的缺点，但是应该使用哪种技术？

Answer 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)

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