您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

SQL Server 預存程序完整實戰指南：從入門到進階參數、返回值與條件邏輯

編輯：Oracle 資料庫基礎

為什麼要使用預存程序？

效能優勢：預編譯執行計畫，減少解析編譯開銷，網路傳輸量小。
封裝業務邏輯：將複雜 SQL、流程控制封裝於資料庫層，應用端僅需呼叫。
安全性：授權執行預存程序而非直接授予表權限，防範 SQL 注入。
可維護性：修改邏輯無需重新部署應用程式。

基礎篇：最簡單的預存程序

CREATE PROCEDURE dbo.GetUserList
AS
BEGIN
    SELECT UserID, UserName FROM Users ORDER BY UserID DESC;
END

ASP.NET (C#) 呼叫範例

public static string GetUserList(ref ArrayList names, ref ArrayList ids)
{
    using (SqlConnection conn = ADConnection.CreateConnection())
    using (SqlCommand cmd = new SqlCommand("GetUserList", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        conn.Open();
        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                if (!string.IsNullOrEmpty(dr[0].ToString()))
                    ids.Add(dr[0].ToString());
                names.Add(dr[1].ToString());
            }
        }
        return "OK";
    }
}

關鍵點：將 CommandType 設為 StoredProcedure，命令文字僅需程序名稱。

進階篇：帶參數的預存程序

CREATE PROCEDURE dbo.GetCommentsByDateRange
    @StartDate VARCHAR(16),
    @EndDate VARCHAR(16)
AS
BEGIN
    SELECT ID FROM Table_AX 
    WHERE CommentDateTime > @StartDate AND CommentDateTime < @EndDate
    ORDER BY ContentOwnerID DESC;
END

使用 SqlDataAdapter 呼叫並填充 DataSet

public static string GetComments(string startDate, string endDate, ref DataSet ds)
{
    using (SqlConnection conn = ADConnection.CreateConnection())
    using (SqlDataAdapter da = new SqlDataAdapter("GetCommentsByDateRange", conn))
    {
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        da.SelectCommand.Parameters.AddWithValue("@StartDate", startDate);
        da.SelectCommand.Parameters.AddWithValue("@EndDate", endDate);
        
        try
        {
            conn.Open();
            da.Fill(ds);
            return "OK";
        }
        catch (Exception ex) { return ex.ToString(); }
    }
}

實用技巧：獲取執行狀態返回值

CREATE PROCEDURE dbo.UpdateUserPassword
    @UserName NVARCHAR(20),
    @PassWord NVARCHAR(20)
AS
BEGIN
    DECLARE @Err INT;
    UPDATE WL_User SET Password = @PassWord WHERE UserName = @UserName;
    SET @Err = @@ERROR;  -- 0 成功，非 0 失敗
    SELECT @Err AS ErrCode;  -- 作為結果集返回
    -- 亦可用 RETURN @Err 透過 ReturnValue 參數接收
END

C# 透過 ExecuteScalar 獲取返回碼

public static string UpdatePassword(string user, string pwd)
{
    using (SqlConnection conn = ADConnection.CreateConnection())
    using (SqlCommand cmd = new SqlCommand("UpdateUserPassword", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@UserName", user);
        cmd.Parameters.AddWithValue("@PassWord", pwd);
        
        conn.Open();
        int result = (int)cmd.ExecuteScalar();  // 獲取第一欄第一列
        return (result == 0) ? "OK" : "FAILED";
    }
}

進階邏輯：條件分支執行不同 SQL

ALTER PROCEDURE dbo.GetCustomerStats
    @CustomerID INT
AS
BEGIN
    IF @CustomerID = -1
    BEGIN
        -- 全部客戶統計
        SELECT ContentOwnerID, UserCName, COUNT(*) AS TotalCount
        FROM View_UserComment
        GROUP BY ContentOwnerID, UserCName
        ORDER BY ContentOwnerID DESC;
    END
    ELSE
    BEGIN
        -- 單一客戶統計
        SELECT ContentOwnerID, UserCName, COUNT(*) AS TotalCount
        FROM View_UserComment
        WHERE ContentOwnerID = @CustomerID
        GROUP BY ContentOwnerID, UserCName
        ORDER BY ContentOwnerID DESC;
    END
END

注意：T-SQL 使用 BEGIN...END 區塊而非大括號，條件判斷靈活支援複雜業務路由。