SQL Server 7.0 入門教學（八）：預存程序回傳結果與錯誤處理

預存程序回傳結果的三種方式

1. 回傳結果集

最通用方式，透過 SELECT 陳述式從永久表、暫存表或區域變數產生。預存程序無法直接存取另一個預存程序的結果集。

範例：從永久表回傳

USE pubs; GO
CREATE PROCEDURE ap_CreateResultFromPermtable AS
SELECT au_lname FROM authors; GO

範例：從區域變數回傳

USE pubs; GO
CREATE PROCEDURE ap_CreateResultFromVariable AS
DECLARE @au_lname CHAR(20)
SELECT @au_lname = au_lname FROM authors WHERE au_id = '172-32-1176'
SELECT @au_lname; GO

2. 設定 OUTPUT 參數

若參數定義為 OUTPUT，對其修改在程序結束後仍保持有效。

USE pubs; GO
CREATE PROCEDURE ap_SetOutputVar @count INT OUTPUT AS
SELECT @count = COUNT(*) FROM authors; GO

-- 呼叫端取得輸出值
CREATE PROCEDURE ap_GetOutputVar AS
DECLARE @num INT
EXECUTE ap_SetOutputVar @num OUTPUT
PRINT 'the count is ' + CONVERT(CHAR, @num); GO

游標作為 OUTPUT 參數： 需指定 VARYING 關鍵字，表示結果集支援輸出參數，提供將結果集回傳給呼叫程序的能力。

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 回傳狀態碼

預存程序總會回傳狀態值，可用 RETURN 自訂狀態碼 (常用於錯誤代碼)。

CREATE PROCEDURE ap_SetReturnStatus AS
DECLARE @count INT
SELECT @count = COUNT(*) FROM authors
IF @count = 0 RETURN(1) ELSE RETURN(0); GO

CREATE PROCEDURE ap_GetReturnStatus AS
DECLARE @status INT
EXECUTE @status = ap_SetReturnStatus
IF @status = 1 PRINT 'No rows found' ELSE PRINT 'successful'; GO

預存程序錯誤處理

系統變數 @@ERROR 在每個 T-SQL 陳述式執行後更新：成功為 0，失敗則含錯誤碼。

錯誤類型

1. 資料庫相關錯誤：由資料不一致引起，非零 @@ERROR 表示具體問題。執行後檢查 @@ERROR，非零則採取行動 (通常返回)。
2. 商務邏輯錯誤：違反商務規則。需先定義規則，再加入檢查碼。常用 RAISERROR 回報，可動態建立訊息或從 sysmessages 取得，並設定 @@ERROR 為自訂錯誤號。

RAISERROR 語法

RAISERROR (msg_id | msg_str, severity, state [, argument ...]) [WITH options]
options: LOG | NOWAIT | SETERROR

• LOG：記入伺服器錯誤日誌與 NT 事件日誌 (需嚴重等級 19-25，限系統管理員)
• NOWAIT：立即傳送訊息至用戶端
• SETERROR：將 @@ERROR 設為 msg_id 或 50000

資料庫錯誤捕捉範例

CREATE PROCEDURE ap_TrapDatabaseError @return_code INT OUTPUT AS
UPDATE authors SET au_lname = 'Jackson' WHERE au_lname = 'Smith'
IF @@ERROR <> 0
BEGIN
    SELECT @return_code = @@ERROR
    RETURN
END
ELSE SELECT @return_code = 0; GO

遠端預存程序呼叫 (RPC)

呼叫駐留於不同伺服器的預存程序。需雙向設定遠端伺服器為有效 RPC 夥伴。

設定步驟

1. 展開伺服器群組 → 右鍵內容 → 勾選「Allow other SQL Servers to connect remotely...」
2. 設定「Query time out」 (預設 0 表無限等待)
3. 重啟服務生效，另一台伺服器重複設定

呼叫語法

EXEC server2.pubs.dbo.myproc

作者後記：本系列針對應用程式開發人員，涵蓋 SQL Server 常用知識。建議深入學習資料庫管理以優化程式效能。