sql - 什么更好，动态 SQL 还是 where case？

Question

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

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

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

动态查询将导致索引扫描。

这种情况会导致seq扫描(即读取整个表)。

所以一定要使用动态查询。