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

存儲過程 vs 動態 SQL：效能、參數化、錯誤處理與 ASP.NET 呼叫實戰

編輯：關於SqlServer

本文從效能、維護性、安全性、除錯等角度比較存儲過程與動態 SQL，並示範帶參數、回傳值、條件邏輯的存儲過程寫法及 ASP.NET (ADO.NET) 呼叫完整代碼。

為何選擇存儲過程？

效能：執行計畫快取重用，減少編譯開銷；網路傳輸僅傳指令名與參數。
封裝邏輯：複雜業務規則集中資料庫層，應用層輕量化。
安全性：授權執行預存程序而非直接授予表權限，防 SQL 注入 (配合參數化)。
維護：修改邏輯無需重新部署應用程式 (視變更範圍)。

最簡存儲過程


CREATE PROCEDURE dbo.testProcedure_AX
AS
SELECT UserID FROM USERS ORDER BY UserID DESC;

ASP.NET 呼叫 (ExecuteReader)


public static string GetCustomerCName(ref ArrayList arrayCName, ref ArrayList arrayID)
{
    using (SqlConnection con = ADConnection.CreateConnection())
    using (SqlCommand cmd = new SqlCommand("testProcedure_AX", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        try
        {
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    if (dr[0].ToString() != "") arrayCName.Add(dr[1].ToString());
                }
            }
            return "OK!";
        }
        catch (Exception ex) { return ex.ToString(); }
    }
}

帶參數存儲過程


CREATE PROCEDURE dbo.AXzhz
    @startDate VARCHAR(16),
    @endDate VARCHAR(16)
AS
SELECT Id FROM Table_AX
WHERE CommentDateTime > @startDate AND CommentDateTime < @endDate
ORDER BY ContentOwnerId DESC;

ASP.NET 呼叫 (SqlDataAdapter + DataSet)


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

回傳執行狀態碼 (OUTPUT / RETURN / SELECT)


CREATE PROCEDURE dbo.AXzhz
    @passWord NVARCHAR(20),
    @userName NVARCHAR(20)
AS
DECLARE @err0 INT;
UPDATE WL_User SET Password = @passWord WHERE UserName = @userName;
SET @err0 = @@ERROR;  -- 2000 風格，2005+ 建議 TRY/CATCH
SELECT @err0 AS ErrCode;  -- 或 RETURN @err0

ASP.NET 取回純量值 (ExecuteScalar)


public static string GetCustomerCName()
{
    using (SqlConnection con = ADConnection.CreateConnection())
    using (SqlCommand cmd = new SqlCommand("AXzhz", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@passWord", "newpwd");
        cmd.Parameters.AddWithValue("@userName", "admin");
        con.Open();
        try
        {
            int re = (int)cmd.ExecuteScalar();
            return (re == 0) ? "OK!" : "false";
        }
        catch (Exception ex) { return ex.ToString(); }
    }
}

條件分支邏輯


ALTER PROCEDURE dbo.SelectCustomerCNameCount
    @customerID INT
AS
IF @customerID = -1
BEGIN
    SELECT ContentOwnerId, UserCName, COUNT(*) AS CountAll
    FROM View_UserComment
    GROUP BY ContentOwnerId, UserCName
    ORDER BY ContentOwnerId DESC;
END
ELSE
BEGIN
    SELECT ContentOwnerId, UserCName, COUNT(*) AS CountAll
    FROM View_UserComment
    WHERE ContentOwnerId = @customerID
    GROUP BY ContentOwnerId, UserCName
    ORDER BY ContentOwnerId DESC;
END

存儲過程 vs 動態 SQL 快速對比

維度	存儲過程	動態 SQL (參數化)
計畫快取	穩定重用	參數化亦可重用，但字串微異即產生新計畫
部署	需資料庫部署權限	隨應用程式部署
除錯	SSMS 逐步除錯較困難	Visual Studio 整合除錯友善
版本控管	需額外腳本管理	原生程式碼版控
跨資料庫	較難 (需連結伺服器)	連接字串切換容易
防注入	原生參數化安全	參數化同樣安全，字串拼接極危險