投稿:mdxy-dxy 字體:[增加 減小] 類型:轉載 時間:2017-06-25 我要評論
這篇文章主要介紹了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'; --執行成功
SP_EXECUTESQL '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, 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=@GroupName'
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, N'@GroupName NVARCHAR(50)',@GroupName
Summary:動態批處理不能訪問定義在批處理裡的局部變量 . SP_EXECUTESQL 可以有輸入輸出參數,比EXECUTE靈活.
下面我們來看看EXECUTE , SP_EXECUTESQL的執行效率,首先把緩存清除執行計劃,然後改變用@GroupName值SuperAdmin、CommonUser、CommonAdmin分別執行三次.然後看看其使用緩存的信息
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的執行效率
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 執行計劃,而用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
執行他就會將內部創建的SQL語句的某個變量的值返回到外部調用者。
主要來源於工作中的一個偶然需要:
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 end
補充資料二、
SQL Server循環執行動態SQL語句.
使用Navicate工具執行查詢成功。
declare @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的正確方式,希望可以幫助到大家。