存儲過程中返回結果
從存儲過程中返回結果有三種方式：
1、 返回結果集
這是客戶端應用程序返回結果的最通用的方法。結果集是通過使用SELECT語句選擇數(shù)據(jù)產(chǎn)生的。結果集可以從永久表、臨時表或局部變量中產(chǎn)生。將結果返回到另一個存儲過程不是一種有效的方法。存儲過程不能訪問另一個存儲過程建立的結果集。
例如從永久表中返回結果集：
USE pubs
GO
CREATE PROCEDURE ap_CreateResultFromPermtable
AS
SELECT au_iname FROM authors
GO
例如從局部變量中創(chuàng)建結果集：
USE pubs
GO
CREATE PROCEDURE ap_CreateResultFromVariable
AS
DECLARE @au_iname char(20)
SELECT @au_iname = au_iname FROM authors
WHERE au_id = ‘172-32-1176’
SELECT @au_id
GO
2、 設置OUTPUT參數(shù)的值
輸出參數(shù)經(jīng)常用來從存儲過程中檢索出結果。如果某個參數(shù)在傳輸?shù)酱鎯^程中時被定義成OUTPUT，則對該參數(shù)的任何修改在退出存儲之后仍然有效。
例如：
USE pubs
GO
CREATE PROCEDURE ap_SetOutputVar @count integer OUTPUT
AS
SELECT @count = count(*) FROM authors
GO
從輸出參數(shù)中檢索出值：
USE pubs
GO
CREATE PROCEDURE ap_GetOutputVar
AS
DECLARE @num integer
EXECUTE ap_SetOutputVar @num OUTPUT
PRINT “the count is”+convert(char,@num)
GO 將游標使用成OUTPUT參數(shù)。游標可以使用OUTPUT（輸出）參數(shù)，但不能使用成輸入?yún)?shù)。也就是說，游標可以作為結果返回，但卻不能傳輸?shù)竭^程中去。當游標被用作參數(shù)時，需要限定其為OUTPUT和VARYING。VARYING關鍵字指出該結果集要用來支持輸出參數(shù)。這樣就提供了將結果集返回到調(diào)用過程的能力。
例如：
USE pubs
GO
CREATE PROCEDURE GetTitleCount @count_cursor CURSOR VARYING OUTPUT
AS
SET @count_cursor = CURSOR
FOR
SELECT au_id,count(*)
FROM titleauthors
GROUP BY au_id
OPEN @count_cursor
GO
3、 通過RETURN參數(shù)返回狀態(tài)
這是一種從存儲過程返回錯誤碼的方法。存儲過程總是返回一個狀態(tài)值，用戶也可以使用RETURN語句返回自己的狀態(tài)。
例如：
USE pubs
GO
CREATE PROCEDURE ap_SetReturnStatus
AS
DECLARE @count integer
SELECT @count = count(*) FROM authors
IF @count = 0
RETURN(1)
ELSE
RETURN (0)
GO
例如檢索出返回的狀態(tài)：
USE pubs
GO
CREATE PROCEDURE ap_GetReturnStatus
AS
DECLARE @status integer
EXECUTE @status = ap_SetReturnStatus
IF @status = 1
PRINT “No rows found”
ELSE
PRINT “successful”
GO
在存儲過程中進行錯誤處理
如同其它程序一樣，在存儲過程中進行錯誤處理是非常重要的。系統(tǒng)變更@@error在執(zhí)行每一個Transact SQL語句之后都會得到一個值。對于成功的執(zhí)行，@@error的值為0，如果出現(xiàn)錯誤，則@@error中將包含錯誤信息。@@error系統(tǒng)變量對存儲過程的錯誤處理是非常重要的。
注意：為了防止錯誤，@@error所能設置的值在sysmessages表的“error”中反映了出來。
在存儲過程中的錯誤有兩種類型：
1、 數(shù)據(jù)庫相關的錯誤
這些錯誤是由數(shù)據(jù)庫的不一致性引起的，系統(tǒng)使用非0的@@error值表示特定的數(shù)據(jù)庫問題。在Transact SQL執(zhí)行之后，可以通過@@error獲得所出現(xiàn)的錯誤。如果發(fā)現(xiàn)@@error不為0，則必須采取必要的行動，大多數(shù)情況下，存儲將不再繼續(xù)進行處理而返回。下面的示例展示了典型的獲取數(shù)據(jù)庫錯誤的方法。該過程將錯誤代碼放置到輸出變量中，這樣，調(diào)用程序就能夠訪問到。
USE pubs
GO
CREATE PROCEDURE ap_TrapDatabaseError @return_code integer OUTPUT
AS
UPDATE authors SET au_iname = “Jackson”
WHERE au_iname = “Smith”
IF @@error <> 0
BEGIN
SELECT @return_code = @@error
RETURN
END
ELSE
@return_code = 0
GO2、 業(yè)務邏輯錯誤
這些錯誤是由于違反了業(yè)務規(guī)則而引起的。要獲取這些錯誤，首先需要定義業(yè)務規(guī)則，基于這些規(guī)則，需要在存儲過程中增加必要的錯誤檢測代碼。人們經(jīng)常使用RAISERROR語句通報這些錯誤。RAISERROR提供了返回用戶定義錯誤及將@@error變量設置成用戶定義錯誤號的能力。錯誤消息可以被動態(tài)地建立，或者基于錯誤號從“sysmessages”表中檢索到。一旦出現(xiàn)了錯誤，錯誤就會以一種服務器錯誤消息的方式返回到客戶機。下面是RAISERROR命令的語法：
RAISERROR (msg_id | msg_str, severity, state
[, argument ][,…n]])
[WITH options]
Msg_id指明用戶定義消息的id，該消息存儲在“sysmessages”系統(tǒng)表中。
Msg_str用于動態(tài)創(chuàng)建消息的消息字符串。這與C語言中的“printf”非常相似。
Severity定義用戶賦值的錯誤消息嚴重程度。
State是從1到127的任意整數(shù)值，它表示錯誤的調(diào)用狀態(tài)信息。負數(shù)的state值將缺省為1。
OPTIONS指明錯誤的定制選項。OPTIONS的有效值如下：
1） LOG。
將錯誤記錄到服務器錯誤日志和NT事件日志中。該選項需要消息帶有從19到25的嚴重程度。而只有系統(tǒng)管理員才能發(fā)出這種消息。
2） NOWAIT。
將消息立即發(fā)送到客戶端服務器。
3） SETERROR。
不管其嚴重級別如何，將@@error的值設置為msg_id或5000。
遠程過程調(diào)用
SQL Server提供了調(diào)用駐留在不同服務器上的存儲過程的能力。調(diào)用這樣的存儲過程稱謂遠程存儲過程調(diào)用。為了使得調(diào)用能從一個SQL Server轉移到另一個服務器，兩個服務器應該相互定義成對方的有效遠程服務器。
設置遠程服務器的配置：
· 擴展某個服務器的組。
· 右擊該服務器并點擊“Properties”。
· 設置選項“Allow other SQL Servers to connect remotely to this SQL server via RPC”。
· 設置“Query time out”選項的值，該值指定從一個查詢處理返回所能等待的秒數(shù)。缺省值為0，表示允許無限的等待時間。
· 設置完成配置選項之后，點擊“OK”。
· 重新啟動服務器之后，修改將會生效。
· 在另一臺遠程服務器上重復相同的步驟。
調(diào)用遠程存儲過程需要指明服務器的名稱，后帶數(shù)據(jù)庫的名稱和擁有者的名稱。下面是在不同的服務器（Server2）上調(diào)用一個存儲過程的示例。
Exec server2.pubs.dbo.myproc
豆豆的后話：
這里只是粗淺的介紹了SQL Server常用的知識，對象也是基于SQL Server數(shù)據(jù)庫編寫應用程序的編程人員，而非數(shù)據(jù)庫管理者。但對于應用程序編程者，了解數(shù)據(jù)庫的管理也是非常有用的。建議在以后的時間自行去了解數(shù)據(jù)庫的管理，這對于優(yōu)化程序也是相當有用的。
從存儲過程中返回結果有三種方式：
1、 返回結果集
這是客戶端應用程序返回結果的最通用的方法。結果集是通過使用SELECT語句選擇數(shù)據(jù)產(chǎn)生的。結果集可以從永久表、臨時表或局部變量中產(chǎn)生。將結果返回到另一個存儲過程不是一種有效的方法。存儲過程不能訪問另一個存儲過程建立的結果集。
例如從永久表中返回結果集：
USE pubs
GO
CREATE PROCEDURE ap_CreateResultFromPermtable
AS
SELECT au_iname FROM authors
GO
例如從局部變量中創(chuàng)建結果集：
USE pubs
GO
CREATE PROCEDURE ap_CreateResultFromVariable
AS
DECLARE @au_iname char(20)
SELECT @au_iname = au_iname FROM authors
WHERE au_id = ‘172-32-1176’
SELECT @au_id
GO
2、 設置OUTPUT參數(shù)的值
輸出參數(shù)經(jīng)常用來從存儲過程中檢索出結果。如果某個參數(shù)在傳輸?shù)酱鎯^程中時被定義成OUTPUT，則對該參數(shù)的任何修改在退出存儲之后仍然有效。
例如：
USE pubs
GO
CREATE PROCEDURE ap_SetOutputVar @count integer OUTPUT
AS
SELECT @count = count(*) FROM authors
GO
從輸出參數(shù)中檢索出值：
USE pubs
GO
CREATE PROCEDURE ap_GetOutputVar
AS
DECLARE @num integer
EXECUTE ap_SetOutputVar @num OUTPUT
PRINT “the count is”+convert(char,@num)
GO 將游標使用成OUTPUT參數(shù)。游標可以使用OUTPUT（輸出）參數(shù)，但不能使用成輸入?yún)?shù)。也就是說，游標可以作為結果返回，但卻不能傳輸?shù)竭^程中去。當游標被用作參數(shù)時，需要限定其為OUTPUT和VARYING。VARYING關鍵字指出該結果集要用來支持輸出參數(shù)。這樣就提供了將結果集返回到調(diào)用過程的能力。
例如：
USE pubs
GO
CREATE PROCEDURE GetTitleCount @count_cursor CURSOR VARYING OUTPUT
AS
SET @count_cursor = CURSOR
FOR
SELECT au_id,count(*)
FROM titleauthors
GROUP BY au_id
OPEN @count_cursor
GO
3、 通過RETURN參數(shù)返回狀態(tài)
這是一種從存儲過程返回錯誤碼的方法。存儲過程總是返回一個狀態(tài)值，用戶也可以使用RETURN語句返回自己的狀態(tài)。
例如：
USE pubs
GO
CREATE PROCEDURE ap_SetReturnStatus
AS
DECLARE @count integer
SELECT @count = count(*) FROM authors
IF @count = 0
RETURN(1)
ELSE
RETURN (0)
GO
例如檢索出返回的狀態(tài)：
USE pubs
GO
CREATE PROCEDURE ap_GetReturnStatus
AS
DECLARE @status integer
EXECUTE @status = ap_SetReturnStatus
IF @status = 1
PRINT “No rows found”
ELSE
PRINT “successful”
GO
在存儲過程中進行錯誤處理
如同其它程序一樣，在存儲過程中進行錯誤處理是非常重要的。系統(tǒng)變更@@error在執(zhí)行每一個Transact SQL語句之后都會得到一個值。對于成功的執(zhí)行，@@error的值為0，如果出現(xiàn)錯誤，則@@error中將包含錯誤信息。@@error系統(tǒng)變量對存儲過程的錯誤處理是非常重要的。
注意：為了防止錯誤，@@error所能設置的值在sysmessages表的“error”中反映了出來。
在存儲過程中的錯誤有兩種類型：
1、 數(shù)據(jù)庫相關的錯誤
這些錯誤是由數(shù)據(jù)庫的不一致性引起的，系統(tǒng)使用非0的@@error值表示特定的數(shù)據(jù)庫問題。在Transact SQL執(zhí)行之后，可以通過@@error獲得所出現(xiàn)的錯誤。如果發(fā)現(xiàn)@@error不為0，則必須采取必要的行動，大多數(shù)情況下，存儲將不再繼續(xù)進行處理而返回。下面的示例展示了典型的獲取數(shù)據(jù)庫錯誤的方法。該過程將錯誤代碼放置到輸出變量中，這樣，調(diào)用程序就能夠訪問到。
USE pubs
GO
CREATE PROCEDURE ap_TrapDatabaseError @return_code integer OUTPUT
AS
UPDATE authors SET au_iname = “Jackson”
WHERE au_iname = “Smith”
IF @@error <> 0
BEGIN
SELECT @return_code = @@error
RETURN
END
ELSE
@return_code = 0
GO2、 業(yè)務邏輯錯誤
這些錯誤是由于違反了業(yè)務規(guī)則而引起的。要獲取這些錯誤，首先需要定義業(yè)務規(guī)則，基于這些規(guī)則，需要在存儲過程中增加必要的錯誤檢測代碼。人們經(jīng)常使用RAISERROR語句通報這些錯誤。RAISERROR提供了返回用戶定義錯誤及將@@error變量設置成用戶定義錯誤號的能力。錯誤消息可以被動態(tài)地建立，或者基于錯誤號從“sysmessages”表中檢索到。一旦出現(xiàn)了錯誤，錯誤就會以一種服務器錯誤消息的方式返回到客戶機。下面是RAISERROR命令的語法：
RAISERROR (msg_id | msg_str, severity, state
[, argument ][,…n]])
[WITH options]
Msg_id指明用戶定義消息的id，該消息存儲在“sysmessages”系統(tǒng)表中。
Msg_str用于動態(tài)創(chuàng)建消息的消息字符串。這與C語言中的“printf”非常相似。
Severity定義用戶賦值的錯誤消息嚴重程度。
State是從1到127的任意整數(shù)值，它表示錯誤的調(diào)用狀態(tài)信息。負數(shù)的state值將缺省為1。
OPTIONS指明錯誤的定制選項。OPTIONS的有效值如下：
1） LOG。
將錯誤記錄到服務器錯誤日志和NT事件日志中。該選項需要消息帶有從19到25的嚴重程度。而只有系統(tǒng)管理員才能發(fā)出這種消息。
2） NOWAIT。
將消息立即發(fā)送到客戶端服務器。
3） SETERROR。
不管其嚴重級別如何，將@@error的值設置為msg_id或5000。
遠程過程調(diào)用
SQL Server提供了調(diào)用駐留在不同服務器上的存儲過程的能力。調(diào)用這樣的存儲過程稱謂遠程存儲過程調(diào)用。為了使得調(diào)用能從一個SQL Server轉移到另一個服務器，兩個服務器應該相互定義成對方的有效遠程服務器。
設置遠程服務器的配置：
· 擴展某個服務器的組。
· 右擊該服務器并點擊“Properties”。
· 設置選項“Allow other SQL Servers to connect remotely to this SQL server via RPC”。
· 設置“Query time out”選項的值，該值指定從一個查詢處理返回所能等待的秒數(shù)。缺省值為0，表示允許無限的等待時間。
· 設置完成配置選項之后，點擊“OK”。
· 重新啟動服務器之后，修改將會生效。
· 在另一臺遠程服務器上重復相同的步驟。
調(diào)用遠程存儲過程需要指明服務器的名稱，后帶數(shù)據(jù)庫的名稱和擁有者的名稱。下面是在不同的服務器（Server2）上調(diào)用一個存儲過程的示例。
Exec server2.pubs.dbo.myproc
豆豆的后話：
這里只是粗淺的介紹了SQL Server常用的知識，對象也是基于SQL Server數(shù)據(jù)庫編寫應用程序的編程人員，而非數(shù)據(jù)庫管理者。但對于應用程序編程者，了解數(shù)據(jù)庫的管理也是非常有用的。建議在以后的時間自行去了解數(shù)據(jù)庫的管理，這對于優(yōu)化程序也是相當有用的。