萬盛學電腦網

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

SQL Server 預存程序入門:建立、參數、輸出與 ASP 呼叫完整範例

預存程序是預先編譯並儲存在資料庫中的 T-SQL 批次,可接受參數、返回結果集與狀態碼,具備效能優化、網路流量減少、安全性封裝等優勢。本文從建立語法到 ASP 呼叫,提供完整入門範例。

一、建立預存程序基本語法

CREATE PROC 預存程序名稱
    @參數1 資料類型 [= 預設值] [OUTPUT],
    @參數2 資料類型 [= 預設值] [OUTPUT],
    ...
AS
BEGIN
    -- T-SQL 陳述式區塊
END
GO
  • CREATE PROCEDURE 可簡寫為 CREATE PROC。
  • 參數名稱必須以 @ 開頭。
  • OUTPUT 關鍵字標示輸出參數,呼叫端可取得修改後的值。

二、實戰範例:依使用者 ID 取得姓名

CREATE PROC upGetUserName
    @intUserId INT,
    @ostrUserName NVARCHAR(20) OUTPUT
AS
BEGIN
    SELECT @ostrUserName = uName FROM uUser WHERE uId = @intUserId;
END
GO

執行一次即在目前資料庫建立 upGetUserName,可在 SSMS「可程式設計」→「預存程序」節點下看到。

三、ASP (ADO) 呼叫預存程序

<%
Dim adoCmd
Set adoCmd = CreateObject("ADODB.Command")
With adoCmd
    .ActiveConnection = adoConn  ' 已開啟的 Connection 物件
    .CommandType = 4             ' adCmdStoredProc = 4
    .CommandText = "upGetUserName"
    .Parameters("@intUserId").Value = 1  ' 傳入參數
    .Execute
    Response.Write "使用者名稱:" & .Parameters("@ostrUserName").Value  ' 取得輸出參數
End With
Set adoCmd = Nothing
%>

關鍵點解析

  1. CommandType = 4:告知 ADO 此命令為預存程序,避免誤解析為 SQL 文字。
  2. Parameters 集合:依參數名稱存取,順序不重要,但名稱必須與定義完全一致(含 @ 符號)。
  3. OUTPUT 參數:Execute 後直接讀取 .Parameters("@ostrUserName").Value 即可。

四、進階議題速覽

  • 回傳結果集:SELECT ... 不指派給變數,ADO 端用 Set rs = cmd.Execute 取得 Recordset。
  • 回傳狀態碼:RETURN @retCode,ADO 端 .Parameters("@RETURN_VALUE") 讀取。
  • 暫時預存程序:#ProcName (Session 級)、##ProcName (全域)。
  • 啟動時自動執行:EXEC sp_procoption 'ProcName', 'startup', 'true'。
  • 加密定義:CREATE PROC ... WITH ENCRYPTION 隱藏原始碼(可被逆向,非絕對安全)。

最佳實踐建議

  1. 命名慣例:up_動作_實體 (如 up_Get_User、up_Ins_Order)。
  2. 一律使用參數化,嚴禁字串拼接防 SQL Injection。
  3. 適度模組化:單一職責、避免超大型程序,利於維護與執行計畫重用。
  4. 加上 SET NOCOUNT ON 減少網路往返。
  5. 定期 sp_recompile 或更新統計資料,避免參數嗅探導致計畫劣化。

掌握預存程序核心語法與 ADO 呼叫模式,即可在傳統 ASP/ASP.NET 應用中高效、安全地操作 SQL Server。

copyright © 萬盛學電腦網 all rights reserved