存儲過程是 SQL Server 封裝業務邏輯、提升性能、增強安全性的核心對象。本文系統梳理調用語法、參數傳遞、返回值處理、動態 SQL 調用、巢狀調用事務行為、錯誤處理模式,並附贈「查找字段屬於哪些表」的實用腳本。
| 場景 | 語法模板 | 關鍵點 |
|---|---|---|
| 無參數 | EXEC dbo.ProcName; | Schema 前綴最佳實踐 |
| 輸入參數 | EXEC dbo.ProcName @Param1 = 'Value', @Param2 = 100; | 命名參數順序無關、可選參數可省略 |
| 輸出參數 | DECLARE @Out INT; EXEC dbo.ProcName @OutParam = @Out OUTPUT; SELECT @Out; | 聲明變量 → OUTPUT 關鍵字 → 調用後讀取變量 |
| 返回值 | DECLARE @Ret INT; EXEC @Ret = dbo.ProcName ...; SELECT @Ret; | INT 類型、默認 0 表示成功、非 0 表示錯誤碼 |
| 結果集 | INSERT INTO #Tmp EXEC dbo.ProcName ...; | 需預先建臨時表匹配列結構 |
-- 1. 創建存儲過程
CREATE PROCEDURE dbo.CalcOrderStats
@CustomerId INT,
@OrderCount INT OUTPUT,
@TotalAmount DECIMAL(18,2) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @OrderCount = COUNT(*), @TotalAmount = ISNULL(SUM(Total), 0)
FROM Orders WHERE CustomerId = @CustomerId;
RETURN 0; -- 成功
END;
GO
-- 2. 調用
DECLARE @Cnt INT, @Amt DECIMAL(18,2), @Ret INT;
EXEC @Ret = dbo.CalcOrderStats @CustomerId = 123, @OrderCount = @Cnt OUTPUT, @TotalAmount = @Amt OUTPUT;
SELECT @Ret AS ReturnCode, @Cnt AS OrderCount, @Amt AS TotalAmount;
DECLARE @ProcName SYSNAME = N'dbo.CalcOrderStats'; DECLARE @Sql NVARCHAR(MAX) = N'EXEC ' + QUOTENAME(@ProcName) + N' @CustomerId = 123, @OrderCount = @Cnt OUT, @TotalAmount = @Amt OUT'; DECLARE @Cnt INT, @Amt DECIMAL(18,2); EXEC sp_executesql @Sql, N'@Cnt INT OUTPUT, @Amt DECIMAL(18,2) OUTPUT', @Cnt OUTPUT, @Amt OUTPUT; SELECT @Cnt, @Amt;
CREATE PROCEDURE dbo.OuterProc AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN;
EXEC dbo.InnerProc1; -- 可能有自己的事務邏輯
EXEC dbo.InnerProc2;
COMMIT TRAN;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRAN;
THROW; -- 重拋異常
END CATCH
END;
BEGIN TRAN/COMMIT,外層 @@TRANCOUNT 會遞增XACT_STATE() = -1 表示不可提交事務,必須回滾SAVE TRANSACTION 部分回滾CREATE PROCEDURE dbo.RobustProc
@Input INT,
@Result VARCHAR(100) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
-- 參數驗證
IF @Input <= 0 THROW 50001, '輸入必須大於 0', 1;
-- 核心邏輯
SELECT @Result = 'Processed: ' + CAST(@Input AS VARCHAR);
RETURN 0;
END TRY
BEGIN CATCH
-- 記錄錯誤日誌(簡化版)
DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE(),
@ErrSeverity INT = ERROR_SEVERITY(),
@ErrState INT = ERROR_STATE();
RAISERROR(@ErrMsg, @ErrSeverity, @ErrState); -- 向客戶端拋錯
RETURN -1; -- 非零返回碼
END CATCH
END;
-- 標準信息視圖查詢(跨資料庫兼容性最好)
SELECT
TABLE_SCHEMA AS SchemaName,
TABLE_NAME AS TableName,
COLUMN_NAME AS ColumnName,
DATA_TYPE AS DataType,
CHARACTER_MAXIMUM_LENGTH AS MaxLength,
IS_NULLABLE AS Nullable
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%TargetColumn%' -- 支援通配符
AND TABLE_CATALOG = 'YourDB' -- 可選:限定資料庫
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;
舊版 sysobjects/syscolumns 寫法僅供維護舊代碼參考,新代碼請用 INFORMATION_SCHEMA 或 sys.objects/sys.columns。
EXEC dbo.ProcName 兩部分命名@Param = Value 命名方式EXEC @Ret = Proc 捕獲,約定 0=成功sp_executesql + 參數化,防注入SET NOCOUNT ON 減少網絡往返