SQL Server聯(lián)機叢書：執(zhí)行存儲過程
2024-08-31 00:47:59
供稿：網(wǎng)友
execute
執(zhí)行標(biāo)量值的用戶定義函數(shù)、系統(tǒng)過程、用戶定義存儲過程或擴展存儲過程。同時支持 transact-sql 批處理內(nèi)的字符串的執(zhí)行
若要喚醒調(diào)用函數(shù)，請使用 execute stored_procedure 中描述的語法。
語法
執(zhí)行存儲過程：[ [ exec [ ute ] ]
{
[ @return_status = ]
{ procedure_name [ ;number ] | @procedure_name_var
}
[ [ @parameter = ] { value | @variable [ output ] | [ default ] ]
[ ,...n ]
[ with recompile ] 執(zhí)行字符串：exec [ ute ] ( { @string_variable | [ n ] 'tsql_string' } [ + ...n ] )參數(shù)
@return_status
是一個可選的整型變量，保存存儲過程的返回狀態(tài)。這個變量在用于 execute 語句前，必須在批處理、存儲過程或函數(shù)中聲明過。
在用于喚醒調(diào)用標(biāo)量值用戶定義函數(shù)時，@return_status 變量可以是任何標(biāo)量數(shù)據(jù)類型。
procedure_name
是擬調(diào)用的存儲過程的完全合法或者不完全合法的名稱。過程名稱必須符合標(biāo)識符規(guī)則。有關(guān)更多信息，請參見使用標(biāo)識符。無論服務(wù)器的代碼頁或排序方式如何，擴展存儲過程的名稱總是區(qū)分大小寫。
用戶可以執(zhí)行在另一數(shù)據(jù)庫中創(chuàng)建的過程，只要該用戶擁有此過程或有在該數(shù)據(jù)庫中執(zhí)行它的適當(dāng)?shù)臋?quán)限。用戶可以在另一臺運行 microsoft® sql server™ 的服務(wù)器上執(zhí)行過程，只要該用戶有適當(dāng)?shù)臋?quán)限使用該服務(wù)器（遠(yuǎn)程訪問），并能在數(shù)據(jù)庫中執(zhí)行該過程。如果指定了服務(wù)器名稱但沒有指定數(shù)據(jù)庫名稱，sql server 會在用戶默認(rèn)的數(shù)據(jù)庫中尋找該過程。
;number
是可選的整數(shù)，用于將相同名稱的過程進行組合，使得它們可以用一句 drop procedure 語句除去。該參數(shù)不能用于擴展存儲過程。
在同一應(yīng)用程序中使用的過程一般都以該方式組合。例如，在訂購應(yīng)用程序中使用的過程可以 orderproc;1、orderproc;2 等來命名。drop procedure orderproc 語句將除去整個組。在對過程分組后，不能除去組中的單個過程。例如，drop procedure orderproc;2 是不允許的。有關(guān)過程組的更多信息，請參見 create procedure。
@procedure_name_var
是局部定義變量名，代表存儲過程名稱。
@parameter
是過程參數(shù)，在 create procedure 語句中定義。參數(shù)名稱前必須加上符號 (@)。在以 @parameter_name = value 格式使用時，參數(shù)名稱和常量不一定按照 create procedure 語句中定義的順序出現(xiàn)。但是，如果有一個參數(shù)使用 @parameter_name = value 格式，則其它所有參數(shù)都必須使用這種格式。
默認(rèn)情況下，參數(shù)可為空。如果傳遞 null 參數(shù)值，且該參數(shù)用于 create 或 alter table 語句中不允許為 null 的列（例如，插入至不允許為 null 的列），sql server 就會報錯。為避免將 null 參數(shù)值傳遞給不允許為 null 的列，可以在過程中添加程序設(shè)計邏輯或采用默認(rèn)值（使用 create 或 alter table 語句中的 default 關(guān)鍵字）。
value
是過程中參數(shù)的值。如果參數(shù)名稱沒有指定，參數(shù)值必須以 create procedure 語句中定義的順序給出。
如果參數(shù)值是一個對象名稱、字符串或通過數(shù)據(jù)庫名稱或所有者名稱進行限制，則整個名稱必須用單引號括起來。如果參數(shù)值是一個關(guān)鍵字，則該關(guān)鍵字必須用雙引號括起來。
如果在 create procedure 語句中定義了默認(rèn)值，用戶執(zhí)行該過程時可以不必指定參數(shù)。如果該過程使用了帶 like 關(guān)鍵字的參數(shù)名稱，則默認(rèn)值必須是常量，并且可以包含 %、_、[ ] 及 [^] 通配符。
默認(rèn)值也可以為 null。通常，過程定義會指定當(dāng)參數(shù)值為 null 時應(yīng)該執(zhí)行的操作。
@variable
是用來保存參數(shù)或者返回參數(shù)的變量。
output
指定存儲過程必須返回一個參數(shù)。該存儲過程的匹配參數(shù)也必須由關(guān)鍵字 output 創(chuàng)建。使用游標(biāo)變量作參數(shù)時使用該關(guān)鍵字。
如果使用 output 參數(shù)，目的是在調(diào)用批處理或過程的其它語句中使用其返回值，則參數(shù)值必須作為變量傳遞（即 @parameter = @variable）。如果一個參數(shù)在 create procedure 語句中不是定義為 output 參數(shù)，則對該參數(shù)指定 output 的過程不能執(zhí)行。不能使用 output 將常量傳遞給存儲過程；返回參數(shù)需要變量名稱。在執(zhí)行過程之前，必須聲明變量的數(shù)據(jù)類型并賦值。返回參數(shù)可以是 text 或 image 數(shù)據(jù)類型以外的任意數(shù)據(jù)類型。
default
根據(jù)過程的定義，提供參數(shù)的默認(rèn)值。當(dāng)過程需要的參數(shù)值沒有事先定義好的默認(rèn)值，或缺少參數(shù)，或指定了 default 關(guān)鍵字，就會出錯。
n
是占位符，表示在它前面的項目可以多次重復(fù)執(zhí)行。例如，execute 語句可以指定一個或者多個 @parameter、value 或 @variable。
with recompile
強制編譯新的計劃。如果所提供的參數(shù)為非典型參數(shù)或者數(shù)據(jù)有很大的改變，使用該選項。在以后的程序執(zhí)行中使用更改過的計劃。該選項不能用于擴展存儲過程。建議盡量少使用該選項，因為它消耗較多系統(tǒng)資源。
@string_variable
是局部變量的名稱。@string_variable 可以是 char、varchar、nchar 或 nvarchar 數(shù)據(jù)類型，最大值為服務(wù)器的可用內(nèi)存。如果字符串長度超過 4,000 個字符，則把多個局部變量串聯(lián)起來用于 execute 字符串。有關(guān)系統(tǒng)提供的 sql server 數(shù)據(jù)類型更多的信息，請參見數(shù)據(jù)類型。
[n]'tsql_string'
是一個常量，tsql_string 可以是 nvarchar 或 varchar 數(shù)據(jù)類型。如果包含 n，則該字符串將解釋為 nvarchar 數(shù)據(jù)類型，最大值為服務(wù)器的可用內(nèi)存。如果字符串長度超過 4,000 個字符，則把多個局部變量串聯(lián)起來用于 execute 字符串。
注釋
如果過程名稱的前三個字符為 sp_，sql server 會在 master 數(shù)據(jù)庫中尋找該過程。如果沒能找到合法的過程名稱，sql server 會尋找所有者名稱為 dbo 的過程。若要將存儲過程名稱解析為與系統(tǒng)存儲過程同名的用戶定義存儲過程，請?zhí)峁┮粋€完全合法的過程名稱。
參數(shù)可以通過利用 value 或 @parameter_name = value 來提供。參數(shù)不是事務(wù)的一個部分；因而如果事務(wù)中的參數(shù)值更改，且該事務(wù)在以后回滾，該參數(shù)值不會退回到以前的值。返回給調(diào)用方的值總是過程返回時的值。
當(dāng)一個存儲過程調(diào)用另一個存儲過程時，會產(chǎn)生嵌套。當(dāng)調(diào)用的過程開始執(zhí)行時，嵌套級會增加，當(dāng)調(diào)用過程執(zhí)行結(jié)束時，嵌套級則會減少。嵌套級最高為32級，超過32級時，會導(dǎo)致整個調(diào)用過程鏈?zhǔn) ．?dāng)前的嵌套級存儲在 @@nestlevel 函數(shù)中。
sql server 目前使用返回值 0 到 -14 來表示存儲過程的執(zhí)行狀態(tài)。值 –15 到 -99 留作后用。有關(guān)保留的返回狀態(tài)值的列表的更多信息，請參見 return。
因為遠(yuǎn)程存儲過程和擴展存儲過程不在事務(wù)的作用域中（除非在 begin distributed transaction 語句中發(fā)出或者是和不同的配置選項一起使用），所以通過調(diào)用執(zhí)行的命令不能回滾。有關(guān)更多信息，請參見系統(tǒng)存儲過程和 begin distributed transaction。
當(dāng)使用游標(biāo)變量時，如果執(zhí)行的過程傳遞一個分配有游標(biāo)的游標(biāo)變量，就會出錯。
在執(zhí)行存儲過程時，如果語句是批處理中的第一個語句，則不一定要指定 execute 關(guān)鍵字。
使用帶字符串的 execute 命令
使用字符串串聯(lián)運算符 (+) 為動態(tài)執(zhí)行創(chuàng)建長字符串。每個字符串表達式可以是 unicode 與 non-unicode 數(shù)據(jù)類型的混合。
盡管每個 [n] 'tsql_string' 或 @string_variable 不得超過 8,000 個字節(jié)，sql server 語法分析器中對這種串聯(lián)只進行邏輯處理而不占用物理內(nèi)存。例如，該語句決不會生成長 16,000 個串聯(lián)起來的字符串： exec('name_of_8000_char_string' + 'another_name_of_8000_char_string')
在 execute 語句執(zhí)行前，不會編譯 execute 語句內(nèi)的語句。
數(shù)據(jù)庫環(huán)境的更改只在 execute 語句結(jié)束前有效。例如，在這個例子的 exec 后，數(shù)據(jù)庫環(huán)境是 master： use master exec ("use pubs") select * from authors
權(quán)限
存儲過程的 execute 權(quán)限默認(rèn)給該存儲過程的所有者，該所有者可以將此權(quán)限轉(zhuǎn)讓給其他用戶。當(dāng)遇到 execute 語句時，即使 execute 語句是在存儲過程中，也會檢查在 execute 字符串內(nèi)使用該語句的權(quán)限。當(dāng)運行一個執(zhí)行字符串的存儲過程時，系統(tǒng)會在執(zhí)行該過程的用戶環(huán)境中，而不是在創(chuàng)建該過程的用戶環(huán)境中檢查權(quán)限。但是，如果某用戶擁有兩個存儲過程，并且第一個過程調(diào)用第二個過程，則在第二個過程中不進行 execute 權(quán)限檢查。
示例a. 使用 execute 傳遞單個參數(shù)
showind 存儲過程需要參數(shù) (@tabname)，它是一個表的名稱。下面這個例子執(zhí)行 showind 存儲過程，以 titles 為參數(shù)值。
說明 showind 存儲過程只是用來作為一個例子，pubs 數(shù)據(jù)庫并沒有此過程。
exec showind titles
在執(zhí)行過程中變量可以顯式命名：
exec showind @tabname = titles
如果這是 isql 腳本或批處理中第一個語句，則 exec 語句可以省略：
showind titles
-或-
showind @tabname = titles
b. 使用多個參數(shù)與一個輸出參數(shù)
這個例子執(zhí)行 roy_check 存儲過程，傳遞三個參數(shù)。第三個參數(shù) @pc 是輸出參數(shù)。過程執(zhí)行完后，返回變量可以從變量@percent
得到。
說明 roy_check 存儲過程只是用作舉例，pubs 數(shù)據(jù)庫中并沒有此過程。
declare @percent intexecute roy_check 'bu1032', 1050, @pc = @percent outputset percent = @percent
c.使用帶一個變量的 execute 'tsql_string' 語句
這個例子顯示 execute 語句如何處理動態(tài)生成的、含有變量的字符串。這個例子創(chuàng)建 tables_cursor 游標(biāo)來保存所有用戶定義表
(type = u) 的列表。
說明 此例子只用作舉例。declare tables_cursor cursor
for
select name from sysobjects where type = 'u'
open tables_cursor
declare @tablename sysname
fetch next from tables_cursor into @tablename
while (@@fetch_status <> -1)
begin
/* a @@fetch_status of -2 means that the row has been deleted.
there is no need to test for this because this loop drops all
user-defined tables. */.
exec ('drop table ' + @tablename)
fetch next from tables_cursor into @tablename
end
print 'all user-defined tables have been dropped from the database.'
deallocate tables_cursor
d.使用帶遠(yuǎn)程存儲過程的 execute 語句
這個例子在遠(yuǎn)程服務(wù)器 sqlserver1 上執(zhí)行 checkcontract 存儲過程，在 @retstat 中保存返回狀態(tài)，說明運行成功或失敗。
declare @retstat int
execute @retstat = sqlserver1.pubs.dbo.checkcontract '409-56-4008'
e. 使用帶擴展存儲過程的 execute 語句
下例使用 xp_cmdshell 擴展存儲過程列出文件擴展名為 .exe 的所有文件的目錄。
use master
execute xp_cmdshell 'dir *.exe'
f. 使用帶一個存儲過程變量的 execute 語句
這個例子創(chuàng)建一個代表存儲過程名稱的變量。
declare @proc_name varchar(30)
set @proc_name = 'sp_who'
exec @proc_name
g. 使用帶 default 的 execute 語句
這個例子創(chuàng)建了一個存儲過程，過程中第一個和第三個參數(shù)為默認(rèn)值。當(dāng)運行該過程時，如果調(diào)用時沒有傳遞值或者指定了默認(rèn)值，
這些默認(rèn)值就會賦給第一個和第三個參數(shù)。注意 default 關(guān)鍵字有多種使用方法。
use pubs
if exists (select name from sysobjects
where name = 'proc_calculate_taxes' and type = 'p')
drop procedure proc_calculate_taxes
go
-- create the stored procedure.
create procedure proc_calculate_taxes (@p1 smallint = 42, @p2 char(1),
@p3 varchar(8) = 'car')
as
select *
from mytable
proc_calculate_taxes 存儲過程可以以多種組合方式執(zhí)行： execute proc_calculate_taxes @p2 = 'a'
execute proc_calculate_taxes 69, 'b'
execute proc_calculate_taxes 69, 'c', 'house'
execute proc_calculate_taxes @p1 = default, @p2 = 'd'
execute proc_calculate_taxes default, @p3 = 'local', @p2 = 'e'
execute proc_calculate_taxes 69, 'f', @p3 = default
execute proc_calculate_taxes 95, 'g', default
execute proc_calculate_taxes default, 'h', default
execute proc_calculate_taxes default, 'i', @p3 = default
