什麼是預存程序?
預存程序是經**預先編譯**儲存在資料庫中的 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 |
| 輸出 | OUTPUT | Proc → Caller | 回傳標量值、狀態旗標 | @RowCount INT OUTPUT |
| 輸入/輸出 | OUTPUT (給初值) | 雙向 | 累加計數、修改後回傳 | @Total INT = 0 OUTPUT |
| 回傳值 | RETURN | Proc → Caller (INT) | 執行狀態碼 (0=成功, 非 0=錯誤代碼) | RETURN @@ERROR |
| 結果集 | (無關鍵字) | Proc → Caller | SELECT 回傳資料集 (可多個) | 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。