分享一下SQL Server執(zhí)行動(dòng)態(tài)SQL的正確方式

SQL Server執(zhí)行動(dòng)態(tài)SQL的話(huà),應(yīng)該如何實(shí)現(xiàn)呢?下面就為您介紹SQL Server執(zhí)行動(dòng)態(tài)SQL兩種正確方式,希望可以讓您對(duì)SQL Server執(zhí)行動(dòng)態(tài)SQL有更深的了解

動(dòng)態(tài)SQL:code that is executed dynamically.它一般是根據(jù)用戶(hù)輸入或外部條件動(dòng)態(tài)組合的SQL語(yǔ)句塊.動(dòng)態(tài)SQL能靈活的發(fā)揮SQL強(qiáng)大的功能、方便的解決一些其它方法難以解決的問(wèn)題.相信使用過(guò)動(dòng)態(tài)SQL的人都能體會(huì)到它帶來(lái)的便利,然而動(dòng)態(tài)SQL有時(shí)候在執(zhí)行性能(效率)上面不如靜態(tài)SQL,而且使用不恰當(dāng),往往會(huì)在安全方面存在隱患(SQL 注入式攻擊).

　　動(dòng)態(tài)SQL可以通過(guò)EXECUTE 或SP_EXECUTESQL這兩種方式來(lái)執(zhí)行.

　　EXECUTE

　　執(zhí)行 Transact-SQL 批中的命令字符串、字符串或執(zhí)行下列模塊之一:系統(tǒng)存儲(chǔ)過(guò)程、用戶(hù)定義存儲(chǔ)過(guò)程、標(biāo)量值用戶(hù)定義函數(shù)或擴(kuò)展存儲(chǔ)過(guò)程.SQL Server 2005 擴(kuò)展了 EXECUTE 語(yǔ)句,以使其可用于向鏈接服務(wù)器發(fā)送傳遞命令.此外,還可以顯式設(shè)置執(zhí)行字符串或命令的上下文

　　SP_EXECUTESQL

　　執(zhí)行可以多次重復(fù)使用或動(dòng)態(tài)生成的 Transact-SQL 語(yǔ)句或批處理.Transact-SQL 語(yǔ)句或批處理可以包含嵌入?yún)?shù).在批處理、名稱(chēng)作用域和數(shù)據(jù)庫(kù)上下文方面,SP_EXECUTESQL 與 EXECUTE 的行為相同.SP_EXECUTESQL stmt 參數(shù)中的 Transact-SQL 語(yǔ)句或批處理在執(zhí)行 SP_EXECUTESQL 語(yǔ)句時(shí)才編譯.隨后,將編譯 stmt 中的內(nèi)容,并將其作為執(zhí)行計(jì)劃運(yùn)行.該執(zhí)行計(jì)劃獨(dú)立于名為 SP_EXECUTESQL 的批處理的執(zhí)行計(jì)劃.SP_EXECUTESQL 批處理不能引用調(diào)用 SP_EXECUTESQL 的批處理中聲明的變量.SP_EXECUTESQL 批處理中的本地游標(biāo)或變量對(duì)調(diào)用 SP_EXECUTESQL 的批處理是不可見(jiàn)的.對(duì)數(shù)據(jù)庫(kù)上下文所作的更改只在 SP_EXECUTESQL 語(yǔ)句結(jié)束前有效.

　　如果只更改了語(yǔ)句中的參數(shù)值,則 sp_executesql 可用來(lái)代替存儲(chǔ)過(guò)程多次執(zhí)行 Transact-SQL 語(yǔ)句.因?yàn)?Transact-SQL 語(yǔ)句本身保持不變,僅參數(shù)值發(fā)生變化,所以 SQL Server 查詢(xún)優(yōu)化器可能重復(fù)使用首次執(zhí)行時(shí)所生成的執(zhí)行計(jì)劃.

　　一般來(lái)說(shuō),我們推薦、優(yōu)先使用SP_EXECUTESQL來(lái)執(zhí)行動(dòng)態(tài)SQL,一方面它更加靈活、可以有輸入輸出參數(shù)、另外一方面,查詢(xún)優(yōu)化器更有可能重復(fù)使用執(zhí)行計(jì)劃,提高執(zhí)行效率.還有就是使用SP_EXECUTESQL能提高安全性;當(dāng)然也不是說(shuō)要完全擯棄EXECUTE,在特定場(chǎng)合下,EXECUTE比SP_EXECUTESQL更方便些,比如動(dòng)態(tài)SQL字符串是VARCHAR類(lèi)型、不是NVARCHAR類(lèi)型.SP_EXECUTESQL 只能執(zhí)行是Unicode的字符串或是可以隱式轉(zhuǎn)換為ntext的常量或變量、而EXECUTE則兩種類(lèi)型的字符串都能執(zhí)行.

　　下面我們來(lái)對(duì)比看看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í)行出錯(cuò)

　　Summary:EXECUTE 可以執(zhí)行非Unicode或Unicode類(lèi)型的字符串常量、變量.而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' 附近有語(yǔ)法錯(cuò)誤.

　　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 括號(hào)里面只能是字符串變量、字符串常量、或它們的連接組合,不能調(diào)用其它一些函數(shù)、存儲(chǔ)過(guò)程等. 如果要使用,則使用變量組合,如上所示.

　　DECLARE @Sql VARCHAR(200);

　　DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';

　　SET@Sql='SELECT * FROM Groups WHEREGroupName=@GroupName'

　　--PRINT @Sql;EXECUTE(@Sql); --出錯(cuò):必須聲明標(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

　　查詢(xún)出來(lái)沒(méi)有結(jié)果,沒(méi)有聲明參數(shù)長(zhǎng)度.

　　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:動(dòng)態(tài)批處理不能訪(fǎng)問(wèn)定義在批處理里的局部變量 . SP_EXECUTESQL 可以有輸入輸出參數(shù),比EXECUTE靈活.

　　下面我們來(lái)看看EXECUTE , SP_EXECUTESQL的執(zhí)行效率,首先把緩存清除執(zhí)行計(jì)劃,然后改變用@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.%';

　　依葫蘆畫(huà)瓢,接著我們看看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 生成了三個(gè)獨(dú)立的 ad hoc 執(zhí)行計(jì)劃,而用SP_EXECUTESQL只生成了一次執(zhí)行計(jì)劃,重復(fù)使用了三次,試想如果一個(gè)庫(kù)里面,有許多這樣類(lèi)似的動(dòng)態(tài)SQL,而且頻繁執(zhí)行,如果采用SP_EXECUTESQL就能提高性能.

以下是其他網(wǎng)友的補(bǔ)充

有些特殊原因，我們需要在SQL語(yǔ)句或者存儲(chǔ)過(guò)程中動(dòng)態(tài)創(chuàng)建SQL語(yǔ)句，然后在SQL語(yǔ)句或存儲(chǔ)過(guò)程中動(dòng)態(tài)來(lái)執(zhí)行。

這里，微軟提供了兩個(gè)方法，一個(gè)是使用

Execute函數(shù)

執(zhí)行方式為
Execute(@sql)來(lái)動(dòng)態(tài)執(zhí)行一個(gè)SQL語(yǔ)句，但是這里的SQL語(yǔ)句無(wú)法得到里面的返回結(jié)果，下面來(lái)介紹另一種方法

使用存儲(chǔ)過(guò)程 sp_ExecuteSql

使用該存儲(chǔ)過(guò)程，則可將動(dòng)態(tài)語(yǔ)句中的參數(shù)返回來(lái)。

比如

declare @sql nvarchar(800),@dd varchar(20)
set @sql='set @mm=''測(cè)試字符串'''
exec sp_executesql @sql,N'@mm varchar(20) output',@dd output
select @dd 

執(zhí)行他就會(huì)將內(nèi)部創(chuàng)建的SQL語(yǔ)句的某個(gè)變量的值返回到外部調(diào)用者。

主要來(lái)源于工作中的一個(gè)偶然需要：

create proc proc_InToServer @收費(fèi)站點(diǎn)編號(hào) varchar(4),@車(chē)道號(hào) tinyint,@進(jìn)入時(shí)間 varchar(23),@UID char(16),
@車(chē)牌 varchar(12),@車(chē)型 char(1),@識(shí)別車(chē)牌號(hào) varchar(12),@識(shí)別車(chē)型 char(1),@收費(fèi)金額 money,@交易狀態(tài) char(1),
@有圖像 bit,@離開(kāi)時(shí)間 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)入時(shí)間),@leaveTime=Convert(datetime,@離開(kāi)時(shí)間)
 set @TableName='ETC03_01_OBE原始過(guò)車(chē)記錄表_'+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+'] (
 [收費(fèi)站點(diǎn)編號(hào)] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [車(chē)道號(hào)] [tinyint] NOT NULL,
 [進(jìn)入時(shí)間] [datetime] NOT NULL,
 [UID] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [車(chē)牌] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
 [車(chē)型] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [識(shí)別車(chē)牌號(hào)] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
 [識(shí)別車(chē)型] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [收費(fèi)金額] [money] NULL ,
 [交易狀態(tài)] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [有圖像] [bit] NOT NULL ,
 [離開(kāi)時(shí)間] [datetime] NULL ,
 [速度] [float] NULL,
    Constraint'+' PK_'+@TableName+' primary key(收費(fèi)站點(diǎn)編號(hào),車(chē)道號(hào),進(jìn)入時(shí)間,UID)
    ) ON [PRIMARY]'
   Execute(@Sql)
  end 
  set @sql = 'select @Cnt=count(*) from '+@TableName+ ' where 收費(fèi)站點(diǎn)編號(hào)='''+@收費(fèi)站點(diǎn)編號(hào)+''' and 車(chē)道號(hào)='+cast(@車(chē)道號(hào) as varchar(4))+' and 進(jìn)入時(shí)間='''+@進(jìn)入時(shí)間+''' and UID='''+@UID+''''
  set @sql = @sql + ' if @Cnt=0 '
  
  set @sql=@sql+'insert '+@TableName+' values('''+@收費(fèi)站點(diǎn)編號(hào)+''','+cast(@車(chē)道號(hào) as varchar(4))+','''+@進(jìn)入時(shí)間+''','''+@Uid+''','''+@車(chē)牌+  
  ''','''+@車(chē)型+''','''+  @識(shí)別車(chē)牌號(hào)+''','''+@識(shí)別車(chē)型+''','+Cast(@收費(fèi)金額 as varchar(8))+','''+@交易狀態(tài)+''','+cast(@有圖像 as varchar(1))+
  ','''+@離開(kāi)時(shí)間+''','+Cast(@速度 as varchar(8))+')'
  --Execute(@sql) 
  exec sp_executesql @sql,N'@Cnt int output',@HasInsert output
end

補(bǔ)充資料二、

SQL Server循環(huán)執(zhí)行動(dòng)態(tài)SQL語(yǔ)句.

使用Navicate工具執(zhí)行查詢(xún)成功。

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執(zhí)行動(dòng)態(tài)SQL的正確方式，希望可以幫助到大家。