SQLServer2008 动态SQL实践

php中文网

发布时间：2016-06-07 15:40:35

1368人浏览过

来源于php中文网

原创

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不是来做这样简单的事情。

Shoping购物网源码

该系统采用多层模式开发，这个网站主要展示女装的经营，更易于网站的扩展和后期的维护，同时也根据常用的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