預存程序是 SQL Server 開發的基石。SQL Server 2005 強化了預存程序的效能與功能,以下三大優勢是選擇它的核心理由。
單次呼叫可執行複雜的多語句邏輯(DML、DDL、控制流),客戶端只發送一條 EXEC 命令,大幅降低網路延遲與封包開銷。
EXECUTE 權限而不給底層資料表權限,實現資料層安全隔離CREATE PROCEDURE [schema].[ProcedureName]
@Param1 DataType = DefaultValue [OUTPUT],
@Param2 DataType = DefaultValue
AS
BEGIN
SET NOCOUNT ON; -- 抑制 DONE_IN_PROC 訊息
-- T-SQL 邏輯
SELECT ...;
INSERT ...;
UPDATE ...;
RETURN @ReturnCode; -- 可選整數回傳碼
END;
| 關鍵字 | 說明 |
|---|---|
OUTPUT | 標記輸出參數,呼叫端可接收回傳值 |
Number (選用) | 同名程序分組編號,DROP PROCEDURE Name;Number 可一次刪除整組 |
@@ERROR | 上一條語句錯誤碼,0 表成功 |
@@IDENTITY | 最後插入的識別欄位值 (跨表/觸發器影響) |
SCOPE_IDENTITY() | 當前範圍最後插入的識別值 (推薦使用) |
CREATE TABLE [dbo].[Col] (
[Col01] VARCHAR(50) NOT NULL PRIMARY KEY,
[Col02] VARCHAR(50) NOT NULL
);
CREATE PROCEDURE [dbo].[Cp_WriteCol]
@Col01 VARCHAR(10) = 'default',
@Col02 VARCHAR(10) = 'default',
@Ret BIT = 1 OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[Col] (Col01, Col02) VALUES (@Col01, @Col02);
INSERT INTO [dbo].[Col] (Col01, Col02) VALUES (@Col01, @Col02);
IF @@ERROR <> 0
SET @Ret = 0;
END;
GO
-- 呼叫測試
DECLARE @Result BIT;
EXEC [dbo].[Cp_WriteCol] @Col01 = 'A', @Col02 = 'B', @Ret = @Result OUTPUT;
SELECT @Result AS ReturnCode; -- 1 成功, 0 失敗
TRY...CATCH 替代 @@ERROR 處理錯誤 (SQL 2005+)預存程序不只是「存放 SQL 的地方」,更是資料庫可程式化、安全化、高效能的核心載體。