這篇文章主要介紹了SQL Server執行動態SQL正確方式,需要的朋友可以參考下
SQL Server執行動態SQL的話,應該如何實現呢?下面就為您介紹SQL Server執行動態SQL兩種正確方式,希望可以讓您對SQL Server執行動態SQL有更深的了解
動態SQL:code that is executed dynamically.它一般是根據用戶輸入或外部條件動態組合的SQL語句塊.動態SQL能靈活的發揮SQL強大的功能、方便的解決一些其它方法難以解決的問題.相信使用過動態SQL的人都能體會到它帶來的便利,然而動態SQL有時候在執行性能(效率)上面不如靜態SQL,而且使用不恰當,往往會在安全方面存在隱患(SQL 注入式攻擊).
動態SQL可以透過EXECUTE 或SP_EXECUTESQL這兩種方式來執行.
EXECUTE
執行Transact-SQL 批次中的命令字串、字串或執行下列模組之一:系統預存程序、使用者定義預存程序、標量值使用者定義函數或擴充預存程序.SQL Server 2005 擴充了EXECUTE 語句,以使其可用於向連結伺服器發送傳遞指令.此外,也可以明確設定執行字串或指令的上下文
#SP_EXECUTESQL
執行可以重複使用或動態產生的Transact-SQL 語句或批次.Transact-SQL 語句或批次可以包含嵌入參數.在批次、名稱作用域和資料庫上下文方面,SP_EXECUTESQL 與EXECUTE 的行為相同.SP_EXECUTESQL stmt 參數中的Transact-SQL 語句或批次在執行SP_EXECUTESQL 語句時才編譯.隨後,將編譯stmt 中的內容,並將其作為執行計劃運行.該執行計劃獨立於名為SP_EXECUTESQL 的批次的執行計劃.SP_EXECUTESQL 批次不能引用調用SP_EXECUTESQL 的批次中聲明的變數.SP_EXECUTESQL 批次中的本地遊標或變數對呼叫SP_EXECUTESQL 的批次是不可見的.資料庫上下文所做的變更只在SP_EXECUTESQL 語句結束前有效.
如果只更改了語句中的參數值,則sp_executesql 可用來代替預存程序多次執行Transact-SQL 語句.因為Transact-SQL 語句本身保持不變,僅參數值變更,所以SQL Server 查詢最佳化器可能重複使用首次執行時所產生的執行計劃.
一般來說,我們推薦、優先使用SP_EXECUTESQL來執行動態SQL,一方面它更加靈活、可以有輸入輸出參數、另一方面,查詢優化器更有可能重複使用執行計劃,提高執行效率.還有就是使用SP_EXECUTESQL能提高安全性;當然也不是說要完全擯棄EXECUTE,在特定場合下,EXECUTE比SP_EXECUTESQL更方便些,比如動態SQL字串是VARCHAR類型、不是NVARCHAR類型.SP_EXECUTESQL 只能執行是Unicode的字串或是可以隱式轉換為ntext的常數或變數、而EXECUTE則兩種類型的字串都能執行.
下面我們來對比看看EXECUTE 和SP_EXECUTESQL的一些細節地方.
EXECUTE(N'SELECT * FROM Groups' ) --執行成功
EXECUTE('SELECT * FROM Groups') --執行成功
SP_EXECUTESQL N'SELECT * FROM Groups'; --SQLSQLTE 'SELECT * FROM Groups' --執行出錯
Summary:EXECUTE 可以執行非Unicode或Unicode類型的字串常數、變數.而SP_EXECUTESQL只能執行Unicode或可以隱式轉換為ntext的字串常數,變數.
DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';
EXECUTE('SELECT * FROM Groups WHERE GroupName=''' + SUBSTRING(@GroupName, 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 括號裡面只能是字串變數、字串常數、或它們的連接組合,不能呼叫其它一些函數、儲存過程等. 如果要使用,則使用變數組合,如上所示.
DECLARE @Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';
## SET@Sql='SELECT * FROM Groups WHEREGroupName=@GroupName' --PRINT @Sql;EXECUTE(@Sql); --出錯:必須宣告標量變數「@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=@GroEGf# # PRINT @Sql; EXEC SP_EXECUTESQL @Sql,N'@GroupNameNVARCHAR',@GroupName查詢出來沒有結果,沒有宣告參數長度.
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; EXEC SP_EXECUTESQL @Sql; 。(50) ,@GroupName Summary:動態批次不能存取定義在批次裡的局部變數. SP_EXECUTESQL 可以有輸入輸出參數,比EXECUTE靈活. 下面我們來看看EXECUTE , SP_EXEXECUTE的執行效率,先把快取清除執行計畫,然後改變用@GroupName值SuperAdmin、CommonUser、CommonAdmin分別執行三次.然後看看其使用快取的資訊 DBCC FREEPROCCACHE; DECLARE @Sql VARCHAR(200); DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin'; --'CommonUser', 'CommonAdmin'SET #SET@Sql ='SECT@Sql ='SECT * FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, '''') EXECUTE(@Sql); SELECTcacheobjtype, objtype, usecounts,WHERE sql NOTLIKE '%cache%'
ANDsql NOTLIKE '%sys.%';
依葫蘆畫瓢,接著我們看看SP_EXECUTESQL
依葫蘆畫瓢,接著我們看看SP_EXECUTESQL#西西西的執行效率# # DBCC FREEPROCCACHE;
DECLARE @Sql NVARCHAR(200); DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin'; --CommonUser', --#CommonUser', ##jonUser', ##nAdmin', --#CommonUser', ##fminonUser', #vonUser', ##fminonUser', ##fmin',d
## SET@Sql ='SELECT * FROM Groups WHERE GroupName=@GroupName' EXECUTESP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)', @GroupName; , objtype, usecounts, sql FROM sys.syscacheobjectsWHERE sql NOTLIKE '%cache%'
AND nOTLIKE '%#%'; # Summary:EXEC 產生了三個獨立的ad hoc 執行計劃,而用SP_EXECUTESQL只產生了一次執行計劃,重複使用了三次,試想如果一個庫裡面,有許多這樣類似的動態SQL,而且頻繁執行,如果採用SP_EXECUTESQL就能提升效能.有些特殊原因，我們需要在SQL語句或儲存過程中動態建立SQL語句，然後在SQL語句或預存程序中動態來執行。
這裡，微軟提供了兩個方法，一個是使用
Execute函數Execute(@sql)來動態執行一個SQL語句，但是這裡的SQL語句無法得到裡面的回傳結果，下面來介紹另一種方法
#使用預存程序
sp_ExecuteSql
##使用該存儲過程，則可將動態語句中的參數傳回。
例如
declare @sql nvarchar(800),@dd varchar(20) set @sql='set @mm=''测试字符串''' exec sp_executesql @sql,N'@mm varchar(20) output',@dd output select @dd
主要來自工作中的一個偶然需求：
create proc proc_InToServer @收费站点编号 varchar(4),@车道号 tinyint,@进入时间 varchar(23),@UID char(16),
@车牌 varchar(12),@车型 char(1),@识别车牌号 varchar(12),@识别车型 char(1),@收费金额 money,@交易状态 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,@进入时间),@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,
[进入时间] [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 ,
[交易状态] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[有图像] [bit] NOT NULL ,
[离开时间] [datetime] NULL ,
[速度] [float] NULL,
Constraint'+' PK_'+@TableName+' primary key(收费站点编号,车道号,进入时间,UID)
) ON [PRIMARY]'
Execute(@Sql)
end
set @sql = 'select @Cnt=count(*) from '+@TableName+ ' where 收费站点编号='''+@收费站点编号+''' and 车道号='+cast(@车道号 as varchar(4))+' and 进入时间='''+@进入时间+''' and UID='''+@UID+''''
set @sql = @sql + ' if @Cnt=0 '
set @sql=@sql+'insert '+@TableName+' values('''+@收费站点编号+''','+cast(@车道号 as varchar(4))+','''+@进入时间+''','''+@Uid+''','''+@车牌+
''','''+@车型+''','''+ @识别车牌号+''','''+@识别车型+''','+Cast(@收费金额 as varchar(8))+','''+@交易状态+''','+cast(@有图像 as varchar(1))+
','''+@离开时间+''','+Cast(@速度 as varchar(8))+')'
--Execute(@sql)
exec sp_executesql @sql,N'@Cnt int output',@HasInsert output
enddeclare @name nvarchar(100) declare @sql nvarchar(200) declare @i int set @i =10000 while @i<=99999 begin set @name = 'test' + cast(@i as varchar(20)) set @sql =N'SELECT * INTO '+ @name +' FROM test' exec sp_executesql @sql print @name set @i=@i + 1 end
以上是講解SQL Server執行相關動態SQL的正確方式的詳細內容。更多資訊請關注PHP中文網其他相關文章！
