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 + '
就是錯誤的，這個想必大家都明白原因，只是寫的時候往往會忽略這個問題，這里tb提醒一下大家。
另一個需要注意的是字符型的變量的判斷，要使用''來判斷是否為空而不能使用 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不是程序代碼在測試的時候會不方便一些，但是它會使程序的執行效率大大提高還是從這一點上說還是值得的。