存儲過程中執(zhí)行動態(tài)Sql語句

存儲過程中執(zhí)行動態(tài)Sql語句SQL為我們提供了兩種動態(tài)執(zhí)行SQL語句的命令，分別是EXEC和sp_executesql;通常,sp_executesqlEXECsp_executesql，能夠重用執(zhí)行計劃，這就大大提供了執(zhí)行性能，還可以編寫更安EXECEXECsp_executesql.EXEC的使用EXEC命令有兩種用法，一種是執(zhí)行一個存儲過程，另一種是執(zhí)行一個動態(tài)的批處理。以下所講的都是第二種用法。下面先使用EXEC演示一個例子,代碼1代碼DECLARE@TableNameVARCHAR(50),@SqlNVARCHAR(MAX),@OrderIDINT;SET@TableName='Orders';SET@OrderID=10251;SET@sql='SELECT*FROM'+QUOTENAME(@TableName)+'WHEREOrderID='+CAST(@OrderIDASVARCHAR(10))+'ORDERBYORDERIDDESC'EXEC(@sql);EXECEXEC：EXEC('SELECTTOP('+CAST(@TopCountASVARCHAR(10))+')*FROM'+QUOTENAME(@TableName)+'ORDERBYORDERIDDESC');SQL編譯器就會報錯，編譯不通過，而如果我們這樣：EXEC(@sql+@sql2+@sql3);編譯器就會通過；EXEC這樣就不會受限制了。EXEC理，如下代碼DECLARE@TableNameVARCHAR(50),@SqlNVARCHAR(MAX),@OrderIDINT;SET@TableName='Orders';SET@OrderID=10251;SET@sql='SELECT*FROM'+QUOTENAME(@TableName)+'WHEREOrderID=@OrderIDORDERBYORDERIDDESC'EXEC(@sql);關(guān)鍵就在SET@sql這一句話中，如果我們運行這個批處理，編譯器就會產(chǎn)生一下錯誤Msg137,Level15,State2,Line1必須聲明標(biāo)量變量"@OrderID"。使用EXEC時，如果您想訪問變量，必須把變量內(nèi)容串聯(lián)到動態(tài)構(gòu)建的代碼字符串中，如：SET@sql='SELECT*FROM'+QUOTENAME(@TableName)+'WHEREOrderID='+CAST(@OrderIDASVARCHAR(10))+'ORDERBYORDERIDDESC'串聯(lián)變量的內(nèi)容也存在性能方面的弊端。SQLServer為每一個的查詢字符串創(chuàng)建新的執(zhí)行計劃，即使查詢模式相同也是這樣。為演示這一點，先清空緩存中的執(zhí)行計劃DBCCFREEPROCCACHE(這個不是本文所涉及的內(nèi)容，您可以查看MS的MSDN)13次，分別對@OrderID3個值，10251，10252，10253。然后使用下面的代碼查詢SELECTcacheobjtype,objtype,usecounts,sqlFROMsys.syscacheobjectsWHEREsqlNOTLIKE'%cach%'ANDsqlNOTLIKE'%sys.%'點擊F5運行，我們可以看到，每執(zhí)行一次都要產(chǎn)生一次的編譯，執(zhí)行計劃沒有得到充分重用。EXECOrders表中所有的記錄數(shù)DECLARE@sqlNVARCHAR(MAX)SET@sql='SELECTCOUNT(ORDERID)FROMOrders';EXEC(@sql);INSERTEXEC語法把輸出插入到一個目標(biāo)表中，然后從這表中獲取值后賦給該變量，就像這樣：代碼DECLARE@sqlNVARCHAR(MAX),@RecordCountINTSET@sql='SELECTCOUNT(ORDERID)FROMOrders';CREATETABLE#T(TIDINT);INSERTINTO#TEXEC(@sql);SET@RecordCount=(SELECTTIDFROM#T)SELECT@RecordCountDROPTABLE#T2sp_executesql 的使用sp_executesql命令在SQLServer中引入的比EXEC命令晚一些，它主要為重用執(zhí)行計劃提供更好的支持。為了和EXEC1EXEC換成sp_executesql,看看是否得到我們所期望的結(jié)果代碼DECLARE@TableNameVARCHAR( 50),@sqlNVARCHAR(MAX),@OrderIDINTNVARCHAR(MAX);SET@TableName= 'Orders'SET@OrderID= 10251;SET@sql= 'SELECT*FROM' +QUOTENAME(@TableName)+ 'WHEREOrderID='+CAST(@OrderIDASVARCHAR( 50))+'ORDERBYORDERIDDESC'EXECsp_executesql@sql注意最后一行；事實證明可以運行；sp_executesql提供接口sp_executesql命令比EXEC命令更靈活，因為它提供一個接口，該接口及支持輸入?yún)?shù)也支持輸出參數(shù)。這功能使你可以創(chuàng)建帶參數(shù)的查詢字符串，這樣就可以比EXEC更好的重用執(zhí)行計劃,sp_executesql的構(gòu)成與存儲過程非常相似，不同之處在于你是動態(tài)構(gòu)建代碼。它的構(gòu)成包括：代碼快，參數(shù)聲明部分，參數(shù)賦值部分。說了這么多，還是看看它的語法：EXECsp_executesql@stmt=<statement>,-- 類似存儲過程主體@params=<params>,--類似存儲過程參數(shù)部分,聲明參數(shù)類型<paramsassignment>-- 通過為參數(shù)指明參數(shù)值的方式為其賦值@stmtsp_executesql;@params參數(shù)與定義輸入/@<paramsassignment>與調(diào)用存儲過程的EXEC部分類似。其實@stmt,@paramsexecsp_executesql的語法就可以簡寫成如下格式：EXECsp_executesql<statement>,<params>,<paramsassignment>sp_executesqlEXEC,EXEC時用到的代碼。代碼DECLARE@TableNameVARCHAR(50),@sqlNVARCHAR(MAX),@OrderIDINT;SET@TableName='Orders';SET@OrderID=10251;SET@sql='SELECT*FROM'+@TableName+'WHEREOrderID=@OIDORDERBYORDERIDDESC'--sql語句的表名實行參數(shù)化時，不可以如下表示：--set@sql='select*from@TableNamewhereOrderID=@OIDORDERBYOrderiddesc',--如果這樣會提示必須聲明標(biāo)量變量@TableName,只可以如上面所寫的一樣，將表名@TableName作為變量名進(jìn)行拼接EXECsp_executesql@sql,N'@OIDint',@OID=@OrderIDexecsp_executesql的執(zhí)行效率，在調(diào)用該代碼和檢查它生成的執(zhí)行計劃前，先清空緩存中的執(zhí)行計劃；DBCCFREEPROCCACHE3次，每次執(zhí)行都賦予@OrderID不同的值，然后查詢sys.syscacheobjects表，并注意它的輸出，優(yōu)化器只創(chuàng)建了一個備用計劃，而且該計劃被重3次SELECTcacheobjtype,objtype,usecounts,sqlFROMsys.syscacheobjectsWHEREsqlNOTLIKE'%cache%'ANDsqlNOTLIKE'%sys.%'ANDsqlNOTLIKE'%sp_executesql%'點擊F5運行。sq_executesql的另一個與其接口有關(guān)的強大功能是，你可以使用輸出參數(shù)為調(diào)用批處理編譯。定義和使用輸出參數(shù)的語法與存儲過程類似。也就是說，你需要在聲明參數(shù)時指定OUTPUT子句。例如，下面的靜態(tài)代碼簡單的演示了如何從動態(tài)批處理中利用輸出參數(shù)@p把值返回到外部批處理中的變量@i.DECLARE@sqlASNVARCHAR(12),@iASINT;SET@sql=N'SET@p=10';EXECsp_executesql@sql,N'@pASINTOUTPUT',@p=@iOUTPUTSELECT@i--該代碼返回輸出10以字母N為前綴標(biāo)識Unicode字符串常量總結(jié)以下幾點：一.使用excesp_executesql效率比exec要高，同一類型的語句，只需編譯一次即可，而exec執(zhí)行幾次就需要編譯幾次。二.sqlwhere子句，也就是條件子句時，exec無法使用變量來進(jìn)行站位，需要sqlSql注入問題，如下：SET@sql='SELECT*FROM'+QUOTENAME(@TableName)+'WHEREOrderID='+CAST(@OrderIDASVARCHAR(50))+'ORDERBYORDERIDDESC'execsp_executesql則可以使用變量來進(jìn)行站位，以后再給這個參數(shù)傳值的sqlSql注入的問題，如下：SET@sql='SELECT*FROM'+@TableName+'WHEREOrderID=@OIDORDERBYORDERIDDESC'三.ExecExec.對 于execsp_executesql來說，不可以將表名參數(shù)和列名參數(shù)在指定為在execsp_executesql參數(shù)聲明部分聲明的參數(shù)，如：代碼createPROCEDUREGetData@tbNamenvarchar(10),@colNamenvarchar(10),@Namenvarchar(10)ASBEGINBEGINdeclare@sqlnvarchar(50);set@s