您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 進階教學 (八)：預存程序回傳結果、錯誤處理與遠端程序呼叫完整實務

編輯：關於SqlServer

本文為 SQL Server 入門系列第八篇，深入探討預存程序的三種回傳機制、系統化錯誤處理模式，以及跨伺服器的遠端程序呼叫設定與實作。

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

1. 回傳結果集 (最常用)

透過 SELECT 陳述式直接輸出，來源可以是永久表、暫存表或區域變數。注意：預存程序無法直接存取另一個預存程序的結果集。

範例 A：從永久表回傳

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

範例 B：從區域變數回傳

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，程序內修改值將在返回呼叫端後保持。適用於單一標量值回傳。

設定端

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 ' + CAST(@num AS VARCHAR);
GO

游標作為 OUTPUT 參數

游標只能 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 回傳狀態碼

預存程序永遠回傳整數狀態 (預設 0 表成功)。自訂錯誤碼慣用 1~127，避免與系統保留碼衝突。

設定狀態碼

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 = 成功，非 0 = 錯誤號碼 (對應 sysmessages 表)
關鍵特性：@@ERROR 只保留「最近一條」陳述式的狀態，必須立即檢查或存入變數

錯誤類型與處理策略

1. 資料庫相關錯誤 (違反約束、型別轉換失敗等)

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;  -- 立即結束，避免後續陳述式覆蓋 @@ERROR
    END
    SELECT @return_code = 0;
GO

2. 業務邏輯錯誤 (違反商業規則)

使用 RAISERROR 主動拋出使用者定義錯誤：

RAISERROR (msg_id | msg_str, severity, state [, argument ...])
[WITH option]

參數	說明
msg_id	sysmessages 表中訊息 ID (50001+ 為使用者自訂)
msg_str	動態訊息字串 (類 C printf 格式)
severity	嚴重等級 1~25 (19+ 需系統管理員權限)
state	1~127，區分相同錯誤的不同觸發點