萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 預存程序完整教學:建立語法、參數模式 (INPUT/OUTPUT/RETURN)、C# 呼叫實戰與系統全域變數速查表

什麼是預存程序?

預存程序是經**預先編譯**儲存在資料庫中的 T-SQL 批次,具備:

  • **效能優勢**:執行計畫快取重用,減少編譯開銷、網路往返 (單次呼叫替代多條 SQL)。
  • **安全性**:使用者僅需 EXECUTE 權限,無需底層資料表直接權限。
  • **封裝性**:商業邏輯集中維護、版本控管、減少前端程式碼。

完整建立語法

CREATE PROC[EDURE] [schema.]procedure_name
    [;number]                                    -- 同名分組編號 (罕用)
    [@parameter datatype [VARYING] [= default] [OUTPUT]] [,...n]
    [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] 
    [FOR REPLICATION]
AS
BEGIN
    SET NOCOUNT ON;                              -- 關閉 DONE_IN_PROC 訊息,提升效能
    -- T-SQL 陳述式
    RETURN integer;                              -- 回傳狀態碼 (0=成功)
END;
GO

參數模式詳細說明

模式關鍵字方向用途範例
輸入(預設)Caller → Proc傳入條件、篩選值@CustID INT
輸出OUTPUTProc → Caller回傳標量值、狀態旗標@RowCount INT OUTPUT
輸入/輸出OUTPUT (給初值)雙向累加計數、修改後回傳@Total INT = 0 OUTPUT
回傳值RETURNProc → Caller (INT)執行狀態碼 (0=成功, 非 0=錯誤代碼)RETURN @@ERROR
結果集(無關鍵字)Proc → CallerSELECT 回傳資料集 (可多個)SELECT * FROM Orders

五大實戰範例

1. 僅回傳結果集

CREATE PROC usp_GetAllUsers AS
    SELECT UserID, UserName, RegDate FROM dbo.Users;
GO
EXEC usp_GetAllUsers;

2. 無參數、無回傳、單純執行 DML

CREATE PROC usp_InsertLog AS
    INSERT INTO dbo.Log (Msg, LogTime) VALUES ('JobRun', GETDATE());
GO
EXEC usp_InsertLog;

3. 回傳狀態碼 (@@ROWCOUNT)

CREATE PROC usp_InsertUser @Name NVARCHAR(50) AS
    INSERT INTO dbo.Users (UserName) VALUES (@Name);
    RETURN @@ROWCOUNT; -- 1 成功, 0 失敗
GO
DECLARE @rc INT; EXEC @rc = usp_InsertUser 'Test'; PRINT @rc;

4. INPUT + OUTPUT 參數

CREATE PROC usp_GetOrderCount @CustID INT, @Cnt INT OUTPUT AS
    SELECT @Cnt = COUNT(*) FROM dbo.Orders WHERE CustomerID = @CustID;
GO
DECLARE @c INT; EXEC usp_GetOrderCount 123, @c OUTPUT; PRINT @c;

5. 綜合:INPUT + OUTPUT + RETURN + 多結果集

CREATE PROC usp_ComplexDemo @Type TINYINT, @OutMsg NVARCHAR(100) OUTPUT AS
    IF @Type = 1 BEGIN
        SELECT * FROM dbo.Products WHERE Active = 1;      -- 結果集 1
        SELECT * FROM dbo.Categories;                     -- 結果集 2
        SET @OutMsg = 'OK';
        RETURN 0;
    END
    ELSE BEGIN
        SET @OutMsg = 'Invalid Type';
        RETURN -1;
    END
GO

C# (ADO.NET) 呼叫完整範本

using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("usp_ComplexDemo", conn) { CommandType = CommandType.StoredProcedure }) {
    // 參數
    cmd.Parameters.Add("@Type", SqlDbType.TinyInt).Value = 1;
    var pOut = cmd.Parameters.Add("@OutMsg", SqlDbType.NVarChar, 100);
    pOut.Direction = ParameterDirection.Output;
    var pRet = cmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
    pRet.Direction = ParameterDirection.ReturnValue;

    conn.Open();
    using (var reader = cmd.ExecuteReader()) {
        // 結果集 1
        while (reader.Read()) { /* ... */ }
        // 結果集 2
        if (reader.NextResult()) {
            while (reader.Read()) { /* ... */ }
        }
    }
    Console.WriteLine($"Output: {pOut.Value}, Return: {pRet.Value}");
}

常用系統全域變數速查表

變數用途範例輸出
@@IDENTITY最後插入的識別欄位值 (跨 Trigger/Session 有風險)1024
SCOPE_IDENTITY()**推薦**:當前範圍最後插入識別值1024
@@ROWCOUNT受影響列數5
@@ERROR上一條語句錯誤碼 (0=成功)0
@@TRANCOUNT目前交易巢狀層級1
@@VERSION版本資訊字串Microsoft SQL Server 2019...
@@SERVERNAME伺服器名稱PROD-SQL-01
@@LANGUAGE目前語言環境us_english
GETDATE()伺服器當前日期時間2024-01-15 14:30:00.123
@@DBTS資料庫目前 timestamp/rowversion 計數器0x0000000001A3F2
@@CONNECTIONS啟動以來嘗試連線總數12543
@@PACK_RECEIVED接收封包數98765
@@PACK_SENT發送封包數98760
@@TOTAL_READ磁碟讀取次數1234567
@@TOTAL_WRITE磁碟寫入次數765432
@@TOTAL_ERRORS磁碟讀寫錯誤數0

最佳實務清單

  • **一律加 `SET NOCOUNT ON`** 減少網路封包。
  • **參數化** 所有輸入值,防 SQL 注入、促進計畫重用。
  • **避免 `SELECT *`**,明確列出欄位。
  • **錯誤處理**:BEGIN TRY...END TRY BEGIN CATCH...THROW; END CATCH (SQL 2012+)。
  • **命名規範**:usp__ (如 usp_Sales_GetOrderDetail)。
  • **版本控管**:.sql 檔納入 Git,部署用 DACPAC 或 Flyway/Liquibase。
copyright © 萬盛學電腦網 all rights reserved