SQL Server的動(dòng)態(tài)SQL功能聽(tīng)說(shuō)了很長(zhǎng)時(shí)間了，但是一直沒(méi)有實(shí)踐過(guò)。通常的項(xiàng)目中都是在程序中拼寫SQL然后送到SQL Server中去執(zhí)行，不過(guò)這樣對(duì)于復(fù)雜一些或者數(shù)據(jù)量大的SQL來(lái)說(shuō)不是最優(yōu)，使用存儲(chǔ)過(guò)程就是一種很好的選擇方案。
一個(gè)最簡(jiǎn)單的動(dòng)態(tài)SQL
exec sp_executesql N'select * from emp'
當(dāng)然我們使用動(dòng)態(tài)SQL不是來(lái)做這樣簡(jiǎn)單的事情。
看看下面這個(gè)，通常我們存儲(chǔ)過(guò)程都是這樣的。
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
但是如果因?yàn)闃I(yè)務(wù)需要傳進(jìn)來(lái)的參數(shù)可能為空，這個(gè)時(shí)候就需要進(jìn)行判斷，但是上面的代碼無(wú)法完成這種需求。我們這里只是一種假設(shè)，實(shí)際的情況可能比這個(gè)復(fù)雜一些。這時(shí)候我們就需要?jiǎng)討B(tài)SQL了。
下面這個(gè)存儲(chǔ)過(guò)程通過(guò)使用動(dòng)態(tài)SQL就很容易實(shí)現(xiàn)了我們程序上的這個(gè)需要。
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
這里要注意一個(gè)問(wèn)題，還是先看例子
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 + '
就是錯(cuò)誤的，這個(gè)想必大家都明白原因，只是寫的時(shí)候往往會(huì)忽略這個(gè)問(wèn)題，這里tb提醒一下大家。
另一個(gè)需要注意的是字符型的變量的判斷，要使用''來(lái)判斷是否為空而不能使用 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'
最后一個(gè)例子，在游標(biāo)中使用動(dòng)態(tài)SQL，因?yàn)樵谟螛?biāo)中不能直接使用動(dòng)態(tài)SQL，所以需要借助臨時(shí)表來(lái)，完成動(dòng)態(tài)SQL在游標(biāo)中的循環(huán)執(zhí)行。
1 BEGIN TRANSACTION
2
3 --定義臨時(shí)表
4 create table #tmp_table
5 (
6 id nchar(5),
7 ...
8
9 )
10
11 --執(zhí)行動(dòng)態(tài)SQL將記錄插入到臨時(shí)表中
12 insert into #tmp_table (id,...) EXECUTE sp_executesql @sql
13
14 --在游標(biāo)中便利游標(biāo)
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
動(dòng)態(tài)SQL使儲(chǔ)存過(guò)程的實(shí)現(xiàn)更加的靈活和方便，但是由于SQL不是程序代碼在測(cè)試的時(shí)候會(huì)不方便一些，但是它會(huì)使程序的執(zhí)行效率大大提高還是從這一點(diǎn)上說(shuō)還是值得的。