SQLServer2008 动态SQL实践

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 <= ''' + @e_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不是程序代码在测试的时候会不方便一些,但是它会使程序的执行效率大大提高还是从这一点上说还是值得的。

posted @ 2012-03-08 15:54 meil 阅读(...) 评论(...) 编辑 收藏