什么更好，动态SQL或在哪里？ [英] What is better, dynamic SQL or where case?

问题描述

我需要创建一个存储过程，该过程需要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）
   声明@ var3 varchar（10）
    SET @ sql ='SELECT * FROM TableDemo1 TD1 WITH（NOLOCK）
    INNER JOIN TableDemo2 TD2 ON TD1.Column1 = TD2.Column2'
   如果（@ var1<> 0和@ var1不为null）
    begin 
    if（@ condition<>''）
    begin 
    set @ condition = @condition +'和TD1.columnTest1='+@var1）
    end 
    else 
    begin 
    set @ condition ='其中TD1.columnTest1 ='+@var1 
    end 
    end 
    if（@ var2<> 0和@ var2不为null）
    begin 
    if（@ condition<>''）
   开始
    set @ condition = @ condition +'和TD2.columnTest2='+@var2）
    end 
    else 
    begin 
    set @ condition ='其中TD2。 columnTest2 = + @ var2 
    end 
    end 
    
    if（@ var3<> 0和@ var3不为空）
    begin 
    if（@ condition< ;>''）
    begin 
    set @ condition = @ condition +'和TD1.columnTest3='+@var3）
    end 
    else 
    begin 
    set @ condition ='其中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和@ var1不为null）$ a 
    
    ;> 0和@ var2不为空）
    THEN 
    CASE WHEN TD2.columnT est2 = @ var2 THEN 1 ELSE 0 END 
    ELSE 1 END）= 1 
    AND 
   （CASE WHEN 
   （@ var3<> 0 AND @ var3 IS NOT NULL）
    THEN 
   案例TD1.columnTest3 = @ var3 
    THEN 1 ELSE 0 END 
    ELSE 1 END）= 1 
     

这只是我的存储过程的一部分，有7-8个表与连接和上面的查询种类条件。

如果我使用动态查询，SQL Server将不得不每次都创建一个执行计划，但如果我使用case where它也使查询慢

我知道动态SQL的缺点，但我应该使用哪种技术？

解决方案一般来说，这取决于，但最常见的是我使用动态查询作为最后的手段。关于你的问题，我可能会用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）
  

索引这不应太慢。

I need to create a stored procedure which takes 12 arguments and the query is filtered with a different combination of this arguments. All 12 arguments are not mandatory as if I pass 3 or 5 or 12 arguments depends on search inputs entered by user.

I can create 2 ways, either using a dynamic SQL query or using 'Case where' statements. Example of these queries are as below:

1. Dynamic Query

   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. Query with 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
   

This is just part of my stored procedure, there are 7-8 tables with joins and as above in query variety in conditions.

If I use a dynamic query, SQL Server will have to create an execution plan every time, but if I use 'case where' it also makes the query slow.

I am aware of disadvantages of dynamic SQL, but which technique I should use?

解决方案

Generally it depends, but most often I use dynamic queries as a last resort. Regarding your question, I would most probably go with the CASE solution, but I think your CASE expressions are unnecessarily complicated. I would replace the WHERE clause with something like this:

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

With proper indexing this shouldn't be too slow.

这篇关于什么更好，动态SQL或在哪里？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！