SQLServer2008动态SQL实践

SQL Server的动态SQL功能听说了很长时间了，但是一直没有实践过。通常的项目中都是在程序中拼写SQL然后送到SQL Server中去执行，不过这样对于复杂一些或者数据量大的SQL来说不是最优，使用存储过程就是一种很好的选择方案。 一个最简单的动态SQL exec sp_exe

SQL Server的动态SQL功能听说了很长时间了，但是一直没有实践过。通常的项目中都是在程序中拼写SQL然后送到SQL Server中去执行，不过这样对于复杂一些或者数据量大的SQL来说不是最优，使用存储过程就是一种很好的选择方案。

一个最简单的动态SQL

 exec  sp_executesql N '  select * from  emp  '  

当然我们使用动态SQL不是来做这样简单的事情。

看看下面这个，通常我们存储过程都是这样的。

  1   CREATE   PROCEDURE   [  dbo  ] . [  mytest  ] 
  2       @id   nchar ( 5 ),
  3       @s_date   nchar ( 10 ),
  4       @e_date   nchar ( 10 )
  5   AS 
  6  
  7   declare   @sql   varchar ( 4000 )
  8  
  9   begin 
 10       select   *   from  emp 
 11       where  work_date  >=   '   + @s_date +   '   and  work_date     '   + @e_date +   ' 
 12   end  

但是如果因为业务需要传进来的参数可能为空，这个时候就需要进行判断，但是上面的代码无法完成这种需求。我们这里只是一种假设，实际的情况可能比这个复杂一些。这时候我们就需要动态SQL了。

下面这个存储过程通过使用动态SQL就很容易实现了我们程序上的这个需要。

 CREATE   PROCEDURE   [  dbo  ] . [  mytest  ] 
     @id   nchar ( 5 ),
     @s_date   nchar ( 10 ),
     @e_date   nchar ( 10 )
 AS 

 declare   @sql   varchar ( 4000 )

 begin 
 set   @sql  =  '  select * from emp   ' 

     if  ( @s_date       '' )  and  ( @e_date       '' )
         set   @sql   =   @sql   +   '      where work_date >=   '''   +   @s_date   +   '''   and work_date   '''   +   @e_date   +   '''' 
     else 
         set   @sql   =   @sql   +   '      where work_date is null  ' 
 end  

这里要注意一个问题，还是先看例子

  1   CREATE   PROCEDURE   [  dbo  ] . [  mytest  ] 
  2    @id   nchar ( 5 ),
  3    @s_date   nchar ( 10 ),
  4    @e_date   nchar ( 10 )
  5   AS 
  6  
  7   declare   @sql   varchar ( 4000 )
  8  
  9   begin 
 10   set   @sql  =  '  select * from emp 
  11    where id=  ''  1  ''   and work_date is null  ' 
 12   end  

注意第11行

set @sql='select * from emp
11 where id= ''1'' and work_date= ''' + @s_date + ''''

如果写成

set @sql='select * from emp
11 where id= '1' and work_date= ' + @s_date + '

就是错误的，这个想必大家都明白原因，只是写的时候往往会忽略这个问题，这里提醒一下大家。

另一个需要注意的是字符型的变量的判断，要使用 '' 来判断是否为空而不能使用 is not null

if (@s_date '' ) and (@e_date '' )
set @sql = @sql + ' where work_date >= ''' + @s_date + ''' and work_date else
set @sql = @sql + ' where work_date is null'

最后一个例子，在游标中使用动态SQL，因为在游标中不能直接使用动态SQL，所以需要借助临时表来，完成动态SQL在游标中的循环执行。

  1   BEGIN   TRANSACTION 
  2  
  3   --  定义临时表  
   4   create   table  #tmp_table 
  5  (
  6   id  nchar ( 5 ),
  7   ...
  8  
  9  )
 10  
 11   --  执行动态SQL将记录插入到临时表中  
  12   insert   into  #tmp_table (id,...)  EXECUTE  sp_executesql  @sql  
 13  
 14     --  在游标中便利游标  
  15     Declare  cur_tmp  Cursor  Scroll
 16        For  
 17       select  (id,...)   from  #tmp_table
 18     OPEN  cur_tmp 
 19  
 20     Fetch   next   from  cur_tmp 
 21  
 22      into   @id ,...
 23     
 24     while   @@fetch_status  =  0 
 25      begin 
 26     
 27  
 28  　　...
 29       fetch   next   from  cur_tmp
 30        into   @id ,...
 31  
 32        
 33     end 
 34     CLOSE  cur_tmp 
 35     drop   table  #tmp_table
 36    
 37     Deallocate  cur_tmp
 38  
 39  
 40  
 41   if   @@error       0 
 42   begin 
 43  
 44    ROLLBACK   TRANSACTION 
 45   
 46    if   not  ( select   object_id ( '  Tempdb..#tmp_table  ' ))  is   null  
 47    drop   table  #tmp_table
 48       
 49   COMMIT   TRANSACTION  

动态SQL使储存过程的实现更加的灵活和方便，但是由于SQL不是程序代码在测试的时候会不方便一些，但是它会使程序的执行效率大大提高还是从这一点上说还是值得的。

查看更多关于SQLServer2008动态SQL实践的详细内容...