SQL Server 存儲過程從入門到實戰：創建、參數、返回值、動態 SQL 與臨時表

存儲過程是 SQL Server 邏輯封裝、效能優化、安全控制的核心單元。本文通過四個遞進實例，涵蓋無參查詢、輸入輸出參數、加密、動態 SQL、臨時表、錯誤處理等核心技能點，並總結三種返回值機制與調用注意事項。

實例 1：最簡查詢存儲過程

CREATE PROC usp_GetAllBooks
AS
    SET NOCOUNT ON;  -- 禁止返回「受影響行數」干擾客戶端
    SELECT BookID, BookName, Price FROM dbo.Books;
GO

EXEC usp_GetAllBooks;

實例 2：帶輸入/輸出參數、加密、事務控制

CREATE PROC usp_AddBook
    @BookID   CHAR(10),
    @BookName VARCHAR(50),
    @Price    MONEY,
    @Total    MONEY OUTPUT
WITH ENCRYPTION  -- 定義文本加密，sp_helptext 無法查看
AS
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRAN;
            INSERT INTO dbo.Books (BookID, BookName, Price)
            VALUES (@BookID, @BookName, @Price);
            SELECT @Total = SUM(Price) FROM dbo.Books;
        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        THROW;  -- 2012+ 重新拋出原始錯誤
    END CATCH
GO

-- 調用示例
DECLARE @Total MONEY;
EXEC usp_AddBook 'B003', 'Delphi 控件開發指南', 120.00, @Total OUTPUT;
PRINT '總金額：' + CAST(@Total AS VARCHAR(20));

實例 3：多表聯結結果存入臨時表（局部暫存表 #table）

CREATE PROC usp_GenSalesTemp
AS
    SET NOCOUNT ON;
    SELECT
        p.ProductID,
        p.ProductName,
        o.CustomerName,
        o.Deposit,
        p.OrderQty * o.Deposit AS TotalAmount
    INTO #TempSales  -- 僅當前會話可見，會話結束自動清理
    FROM dbo.Products p
    INNER JOIN dbo.Orders o ON p.ProductID = o.ProductID;

    IF @@ERROR = 0
        PRINT 'Success';
    ELSE
        PRINT 'Fail';
GO

臨時表類型對比：

• #Temp（局部）：當前會話私有，巢狀存儲過程可見，會話結束自動 Drop。
• ##Temp（全局）：所有會話可見，創建會話結束後 Drop，慎用（併發衝突）。
• @Table 變量：批次/存儲過程作用域，內存優先（小數據量），無統計資料，不支援並行計畫。

三種返回值機制對比

進階技巧：動態 SQL 安全構建

CREATE PROC usp_SearchBooks
    @Keyword NVARCHAR(100) = NULL,
    @MinPrice MONEY = NULL,
    @MaxPrice MONEY = NULL
AS
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX) = N'SELECT BookID, BookName, Price FROM dbo.Books WHERE 1=1';
    DECLARE @params NVARCHAR(MAX) = N'@Keyword NVARCHAR(100), @MinPrice MONEY, @MaxPrice MONEY';

    IF @Keyword IS NOT NULL
        SET @sql += N' AND BookName LIKE @Keyword';
    IF @MinPrice IS NOT NULL
        SET @sql += N' AND Price >= @MinPrice';
    IF @MaxPrice IS NOT NULL
        SET @sql += N' AND Price <= @MaxPrice';

    -- 參數化執行，防注入、復用計畫
    EXEC sp_executesql @sql, @params, @Keyword, @MinPrice, @MaxPrice;
GO

調試與部署檢查清單

• [ ] 所有存儲過程首行 SET NOCOUNT ON。
• [ ] 參數預設值合理（@Param TYPE = DEFAULT）。
• [ ] 修改操作包裹 TRY/CATCH + 顯式事務。
• [ ] 動態 SQL 僅用 sp_executesql + 參數化，拒絕字符串拼接 EXEC(@sql)。
• [ ] 臨時表用 #，大結果集考慮 @Table 變量。
• [ ] 輸出參數/返回值語義清晰，文檔化。
• [ ] 權限最小化：GRANT EXEC ON usp_* TO AppRole，不授予表直連權限。