這篇文章主要介紹了sql server執(zhí)行動態(tài)sql正確方式,需要的朋友可以參考下
SQL Server執(zhí)行動態(tài)SQL的話,應(yīng)該如何實現(xiàn)呢?下面就為您介紹SQL Server執(zhí)行動態(tài)SQL兩種正確方式,希望可以讓您對SQL Server執(zhí)行動態(tài)SQL有更深的了解
動態(tài)SQL:code that is executed dynamically.它一般是根據(jù)用戶輸入或外部條件動態(tài)組合的SQL語句塊.動態(tài)SQL能靈活的發(fā)揮SQL強(qiáng)大的功能、方便的解決一些其它方法難以解決的問題.相信使用過動態(tài)SQL的人都能體會到它帶來的便利,然而動態(tài)SQL有時候在執(zhí)行性能(效率)上面不如靜態(tài)SQL,而且使用不恰當(dāng),往往會在安全方面存在隱患(SQL 注入式攻擊).
動態(tài)SQL可以通過EXECUTE 或SP_EXECUTESQL這兩種方式來執(zhí)行.
EXECUTE
執(zhí)行 Transact-SQL 批中的命令字符串、字符串或執(zhí)行下列模塊之一:系統(tǒng)存儲過程、用戶定義存儲過程、標(biāo)量值用戶定義函數(shù)或擴(kuò)展存儲過程.SQL Server 2005 擴(kuò)展了 EXECUTE 語句,以使其可用于向鏈接服務(wù)器發(fā)送傳遞命令.此外,還可以顯式設(shè)置執(zhí)行字符串或命令的上下文
SP_EXECUTESQL
執(zhí)行可以多次重復(fù)使用或動態(tài)生成的 Transact-SQL 語句或批處理.Transact-SQL 語句或批處理可以包含嵌入?yún)?shù).在批處理、名稱作用域和數(shù)據(jù)庫上下文方面,SP_EXECUTESQL 與 EXECUTE 的行為相同.SP_EXECUTESQL stmt 參數(shù)中的 Transact-SQL 語句或批處理在執(zhí)行 SP_EXECUTESQL 語句時才編譯.隨后,將編譯 stmt 中的內(nèi)容,并將其作為執(zhí)行計劃運行.該執(zhí)行計劃獨立于名為 SP_EXECUTESQL 的批處理的執(zhí)行計劃.SP_EXECUTESQL 批處理不能引用調(diào)用 SP_EXECUTESQL 的批處理中聲明的變量.SP_EXECUTESQL 批處理中的本地游標(biāo)或變量對調(diào)用 SP_EXECUTESQL 的批處理是不可見的.對數(shù)據(jù)庫上下文所作的更改只在 SP_EXECUTESQL 語句結(jié)束前有效.
如果只更改了語句中的參數(shù)值,則 sp_executesql 可用來代替存儲過程多次執(zhí)行 Transact-SQL 語句.因為 Transact-SQL 語句本身保持不變,僅參數(shù)值發(fā)生變化,所以 SQL Server 查詢優(yōu)化器可能重復(fù)使用首次執(zhí)行時所生成的執(zhí)行計劃.
一般來說,我們推薦、優(yōu)先使用SP_EXECUTESQL來執(zhí)行動態(tài)SQL,一方面它更加靈活、可以有輸入輸出參數(shù)、另外一方面,查詢優(yōu)化器更有可能重復(fù)使用執(zhí)行計劃,提高執(zhí)行效率.還有就是使用SP_EXECUTESQL能提高安全性;當(dāng)然也不是說要完全擯棄EXECUTE,在特定場合下,EXECUTE比SP_EXECUTESQL更方便些,比如動態(tài)SQL字符串是VARCHAR類型、不是NVARCHAR類型.SP_EXECUTESQL 只能執(zhí)行是Unicode的字符串或是可以隱式轉(zhuǎn)換為ntext的常量或變量、而EXECUTE則兩種類型的字符串都能執(zhí)行.
下面我們來對比看看EXECUTE 和SP_EXECUTESQL的一些細(xì)節(jié)地方.
EXECUTE(N’SELECT * FROM Groups’) –執(zhí)行成功
EXECUTE(‘SELECT * FROM Groups’) –執(zhí)行成功
SP_EXECUTESQL N’SELECT * FROM Groups’; –執(zhí)行成功
SP_EXECUTESQL ‘SELECT * FROM Groups’ –執(zhí)行出錯
Summary:EXECUTE 可以執(zhí)行非Unicode或Unicode類型的字符串常量、變量.而SP_EXECUTESQL只能執(zhí)行Unicode或可以隱式轉(zhuǎn)換為ntext的字符串常量、變量.
DECLARE @GroupName VARCHAR(50);SET@GroupName =’SuperAdmin’;
EXECUTE(‘SELECT * FROM Groups WHERE GroupName=”’ + SUBSTRING(@GroupName, 1,5) + ””); –‘SUBSTRING’ 附近有語法錯誤.
DECLARE @Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);SET@GroupName =’SuperAdmin’;
SET@Sql=’SELECT * FROM Groups WHERE GroupName=”’ + SUBSTRING(@GroupName, 1,5) + ””
–PRINT @Sql;EXECUTE(@Sql);
Summary:EXECUTE 括號里面只能是字符串變量、字符串常量、或它們的連接組合,不能調(diào)用其它一些函數(shù)、存儲過程等. 如果要使用,則使用變量組合,如上所示.
DECLARE @Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);SET@GroupName =’SuperAdmin’;
SET@Sql=’SELECT * FROM Groups WHEREGroupName=@GroupName’
–PRINT @Sql;EXECUTE(@Sql); –出錯:必須聲明標(biāo)量變量 “@GroupName”.SET@Sql=’SELECT * FROM Groups WHERE GroupName=’ + QUOTENAME(@GroupName, ””)
EXECUTE(@Sql); –正確:
DECLARE @Sql NVARCHAR(200);
DECLARE @GroupName NVARCHAR(50);SET@GroupName =’SuperAdmin’;
SET@Sql=’SELECT * FROM Groups WHEREGroupName=@GroupName’
PRINT @Sql;
EXEC SP_EXECUTESQL @Sql,N’@GroupNameNVARCHAR’,@GroupName
查詢出來沒有結(jié)果,沒有聲明參數(shù)長度.
DECLARE @Sql NVARCHAR(200);
DECLARE @GroupName NVARCHAR(50);SET@GroupName =’SuperAdmin’;
SET@Sql =’SELECT * FROM Groups WHERE GroupName=@GroupName’
PRINT @Sql;
EXEC SP_EXECUTESQL @Sql, N’@GroupName NVARCHAR(50)’,@GroupName
Summary:動態(tài)批處理不能訪問定義在批處理里的局部變量 . SP_EXECUTESQL 可以有輸入輸出參數(shù),比EXECUTE靈活.
下面我們來看看EXECUTE , SP_EXECUTESQL的執(zhí)行效率,首先把緩存清除執(zhí)行計劃,然后改變用@GroupName值SuperAdmin、CommonUser、CommonAdmin分別執(zhí)行三次.然后看看其使用緩存的信息
DBCC FREEPROCCACHE;
DECLARE @Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);SET@GroupName =’SuperAdmin’; –‘CommonUser’, ‘CommonAdmin’
SET@Sql =’SELECT * FROM Groups WHERE GroupName=’ + QUOTENAME(@GroupName, ””)
EXECUTE(@Sql); SELECTcacheobjtype, objtype, usecounts, sql
FROM sys.syscacheobjects
WHERE sql NOTLIKE ‘%cache%’
ANDsql NOTLIKE ‘%sys.%’;
依葫蘆畫瓢,接著我們看看SP_EXECUTESQL的執(zhí)行效率
DBCC FREEPROCCACHE;
DECLARE @Sql NVARCHAR(200);
DECLARE @GroupName NVARCHAR(50);SET@GroupName =’SuperAdmin’; –‘CommonUser’, ‘CommonAdmin’
SET@Sql =’SELECT * FROM Groups WHERE GroupName=@GroupName’
EXECUTESP_EXECUTESQL @Sql, N’@GroupName NVARCHAR(50)’, @GroupName;
SELECTcacheobjtype, objtype, usecounts, sql
FROM sys.syscacheobjects
WHERE sql NOTLIKE ‘%cache%’
ANDsql NOTLIKE ‘%sys.%’;
Summary:EXEC 生成了三個獨立的 ad hoc 執(zhí)行計劃,而用SP_EXECUTESQL只生成了一次執(zhí)行計劃,重復(fù)使用了三次,試想如果一個庫里面,有許多這樣類似的動態(tài)SQL,而且頻繁執(zhí)行,如果采用SP_EXECUTESQL就能提高性能.
以下是其他網(wǎng)友的補充
有些特殊原因，我們需要在SQL語句或者存儲過程中動態(tài)創(chuàng)建SQL語句，然后在SQL語句或存儲過程中動態(tài)來執(zhí)行。
這里，微軟提供了兩個方法，一個是使用
Execute函數(shù)
執(zhí)行方式為
Execute(@sql)來動態(tài)執(zhí)行一個SQL語句，但是這里的SQL語句無法得到里面的返回結(jié)果，下面來介紹另一種方法
使用存儲過程 sp_ExecuteSql
使用該存儲過程，則可將動態(tài)語句中的參數(shù)返回來。
比如
declare?@sql?nvarchar(800),@dd?varchar(20) set?@sql='set?@mm=''測試字符串''' exec?sp_executesql?@sql,N'@mm?varchar(20)?output',@dd?output select?@dd
執(zhí)行他就會將內(nèi)部創(chuàng)建的SQL語句的某個變量的值返回到外部調(diào)用者。
主要來源于工作中的一個偶然需要：
create?proc?proc_InToServer?@收費站點編號?varchar(4),@車道號?tinyint,@進(jìn)入時間?varchar(23),@UID?char(16), @車牌?varchar(12),@車型?char(1),@識別車牌號?varchar(12),@識別車型?char(1),@收費金額?money,@交易狀態(tài)?char(1), @有圖像?bit,@離開時間?varchar(23),@速度?float,@HasInsert?int?output as begin ??declare?@inTime?datetime,@TableName?varchar(255),@leaveTime?datetime,@HasTable?bit,@Sql?nvarchar(4000) ?select?@intime=Convert(datetime,@進(jìn)入時間),@leaveTime=Convert(datetime,@離開時間) ?set?@TableName='ETC03_01_OBE原始過車記錄表_'+dbo.formatDatetime(@intime,'YYYYMMDD') ?select?@HasTable=(Case?when?Count(*)>0?then?1?else?0?end)?from?sysobjects?where?id=Object_id(@TableName)?and?ObjectProperty(id,'IsUserTable')=1 ?if?@HasTable=0 ?begin ??set?@Sql='CREATE?TABLE?[dbo].['+@TableName+']?( ?[收費站點編號]?[char]?(4)?COLLATE?Chinese_PRC_CI_AS?NOT?NULL, ?[車道號]?[tinyint]?NOT?NULL, ?[進(jìn)入時間]?[datetime]?NOT?NULL, ?[UID]?[char]?(16)?COLLATE?Chinese_PRC_CI_AS?NOT?NULL, ?[車牌]?[varchar]?(12)?COLLATE?Chinese_PRC_CI_AS?NULL?, ?[車型]?[char]?(1)?COLLATE?Chinese_PRC_CI_AS?NULL?, ?[識別車牌號]?[varchar]?(12)?COLLATE?Chinese_PRC_CI_AS?NULL?, ?[識別車型]?[char]?(1)?COLLATE?Chinese_PRC_CI_AS?NULL?, ?[收費金額]?[money]?NULL?, ?[交易狀態(tài)]?[char]?(1)?COLLATE?Chinese_PRC_CI_AS?NULL?, ?[有圖像]?[bit]?NOT?NULL?, ?[離開時間]?[datetime]?NULL?, ?[速度]?[float]?NULL, ????Constraint'+'?PK_'+@TableName+'?primary?key(收費站點編號,車道號,進(jìn)入時間,UID) ????)?ON?[PRIMARY]' ???Execute(@Sql) ??end? ??set?@sql?=?'select?@Cnt=count(*)?from?'+@TableName+?'?where?收費站點編號='''+@收費站點編號+'''?and?車道號='+cast(@車道號?as?varchar(4))+'?and?進(jìn)入時間='''+@進(jìn)入時間+'''?and?UID='''+@UID+'''' ??set?@sql?=?@sql?+?'?if?@Cnt=0?' ?? ??set?@sql=@sql+'insert?'+@TableName+'?values('''+@收費站點編號+''','+cast(@車道號?as?varchar(4))+','''+@進(jìn)入時間+''','''+@Uid+''','''+@車牌+?? ??''','''+@車型+''','''+??@識別車牌號+''','''+@識別車型+''','+Cast(@收費金額?as?varchar(8))+','''+@交易狀態(tài)+''','+cast(@有圖像?as?varchar(1))+ ??','''+@離開時間+''','+Cast(@速度?as?varchar(8))+')' ??--Execute(@sql)? ??exec?sp_executesql?@sql,N'@Cnt?int?output',@HasInsert?output end
補充資料二、
SQL Server循環(huán)執(zhí)行動態(tài)SQL語句.
使用Navicate工具執(zhí)行查詢成功。
declare?@name?nvarchar(100) declare?@sql?nvarchar(200) declare?@i?int set?@i?=10000 while?@i
.png)
