🧾SQL Server Stored Procedure 怎麼寫？SSMS 新手必學的預存程序教學（含範例與常見錯誤）

-

什麼是 Stored Procedure（預存程序）？用白話解釋

如果你把 SQL 想像成「你跟資料庫說要做什麼的指令」，那 Stored Procedure 就像是：

把一串常用的 SQL 步驟「打包成一個按鈕」，以後只要按一次就會自動完成。

對完全沒寫過的人來說，你可以把它想成：

  • Excel 的巨集（Macro）：把一堆動作錄起來，以後一鍵執行
  • 咖啡機按鈕：你不需要每次研究怎麼磨豆、幾度水溫，你只要按「拿鐵」就會出來

Stored Procedure 的好處

  1. 可重複使用：同樣的查詢/更新不用每次重寫
  2. 較好維護：邏輯集中在資料庫端，改一次所有呼叫者都更新
  3. 安全性更好：可以只授權執行 SP，不必給使用者表格權限
  4. 效能常常更好：SQL Server 會對 SP 做最佳化（例如執行計畫快取）

在 SSMS 裡 Stored Procedure 通常怎麼寫？

你在 SSMS 裡新增一個 Stored Procedure，本質上就是寫一段 T-SQL，常見結構是：

  • CREATE PROCEDURE：建立新的 SP
  • ALTER PROCEDURE：修改既有 SP
  • EXEC：執行 SP
  • BEGIN...END：把多行邏輯包起來
  • GO：告訴 SSMS「這段到這裡是一個批次」

範例 1：最簡單的 Stored Procedure（無參數）

假設你常常要查詢最近 50 筆訂單：

CREATE PROCEDURE dbo.usp_Order_GetRecent
AS
BEGIN
    SELECT TOP (50)
           OrderId, CustomerName, OrderDate, TotalAmount
    FROM dbo.Orders
    ORDER BY OrderDate DESC;
END;
GO

怎麼執行？

EXEC dbo.usp_Order_GetRecent;

📌 新手最常忽略：GO 不是 SQL 語法，是 SSMS 的批次分隔符。

範例 2：帶參數（最常見）

想查某個客戶的訂單：

CREATE PROCEDURE dbo.usp_Order_GetByCustomer
    @CustomerId INT
AS
BEGIN
    SELECT OrderId, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC;
END;
GO

呼叫方式：

EXEC dbo.usp_Order_GetByCustomer @CustomerId = 101;

📌 觀念很重要：
參數就是你給這個「按鈕」的輸入值，例如客戶編號。

範例 3：參數有預設值（更貼近真實系統）

有時候你想提供預設條件，例如只看「已付款」： 

CREATE PROCEDURE dbo.usp_Order_Search
    @CustomerId INT,
    @PaidStatus CHAR(1) = 'Y'
AS
BEGIN
    SELECT OrderId, OrderDate, PaidStatus, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
      AND PaidStatus = @PaidStatus;
END;
GO

呼叫：

EXEC dbo.usp_Order_Search @CustomerId = 101;
-- PaidStatus 沒給就用預設 'Y'

範例 4：更新資料（UPDATE）— 很多企業系統都靠這個

例如更新會員的 Email：

CREATE PROCEDURE dbo.usp_Member_UpdateEmail
    @MemberId INT,
    @NewEmail NVARCHAR(200)
AS
BEGIN
    UPDATE dbo.Members
    SET Email = @NewEmail,
        UpdatedAt = SYSDATETIME()
    WHERE MemberId = @MemberId;
END;
GO

呼叫：

EXEC dbo.usp_Member_UpdateEmail @MemberId = 9, @NewEmail = N'test@example.com';

範例 5：回傳結果（OUTPUT 參數）— 常用來回傳單一值

很多時候前端或後端想知道「這個客戶有幾筆訂單」： 

CREATE PROCEDURE dbo.usp_Order_CountByCustomer
    @CustomerId INT,
    @OrderCount INT OUTPUT
AS
BEGIN
    SELECT @OrderCount = COUNT(*)
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

使用：

DECLARE @Cnt INT;

EXEC dbo.usp_Order_CountByCustomer
     @CustomerId = 101,
     @OrderCount = @Cnt OUTPUT;

SELECT @Cnt AS OrderCount;

📌 新手必踩雷：

  • 你在 EXEC 呼叫時，OUTPUT 參數最後一定要寫 OUTPUT

範例 6：交易（Transaction）+ 錯誤處理（企業必備）

像「扣庫存、寫訂單」這種動作，只要任何一步失敗，就要整筆回滾，避免資料不一致。 

CREATE PROCEDURE dbo.usp_Order_CreateAndDeductStock
    @ProductId INT,
    @Qty INT,
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRAN;

        -- 1) 檢查庫存是否足夠
        IF EXISTS (
            SELECT 1
            FROM dbo.Inventory
            WHERE ProductId = @ProductId
              AND StockQty >= @Qty
        )
        BEGIN
            -- 2) 扣庫存
            UPDATE dbo.Inventory
            SET StockQty = StockQty - @Qty
            WHERE ProductId = @ProductId;

            -- 3) 建訂單
            INSERT INTO dbo.Orders(CustomerId, OrderDate, TotalAmount)
            VALUES (@CustomerId, SYSDATETIME(), 0);
        END
        ELSE
        BEGIN
            -- 自訂錯誤：庫存不足
            THROW 50001, 'Insufficient stock.', 1;
        END

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        THROW;
    END CATCH
END;
GO

📌 你可以把它理解成：

  • BEGIN TRAN：開始「一整包動作」
  • COMMIT：全部成功才確定
  • ROLLBACK：任何一步錯就全部取消

如何修改或刪除 Stored Procedure？

修改（ALTER）

ALTER PROCEDURE dbo.usp_Order_GetRecent
AS
BEGIN
    SELECT TOP (20) OrderId, OrderDate
    FROM dbo.Orders
    ORDER BY OrderDate DESC;
END;
GO

刪除（DROP）

DROP PROCEDURE dbo.usp_Order_GetRecent;

新手最常見 6 個錯誤（你可以直接對照排查）

  1. 忘記寫 GO，導致建立失敗或混在同一批次
  2. OUTPUT 參數呼叫忘記加 OUTPUT
  3. 把 RETURN 當成回傳資料（其實通常只用來回傳狀態碼）
  4. 沒寫 SET NOCOUNT ON，應用程式可能多收到「影響幾列」訊息
  5. UPDATE/INSERT 沒包交易，資料容易寫一半就壞掉
  6. 沒有 TRY/CATCH，錯誤訊息不清楚也不容易追

結語：Stored Procedure 其實就是「把 SQL 打包成可重複使用的功能」

如果你剛開始學 SQL Server，請先掌握三件事就夠用 80%：

  • 會寫 CREATE/ALTER PROCEDURE
  • 會用參數（Input + Output）
  • 會用交易 + TRY/CATCH 保護資料一致性

留言

張貼留言

這個網誌中的熱門文章

🔍Vue.js 專案錯誤排查：解決 numericFields is not defined 與合併儲存格邏輯最佳化

-
  前言 在前端開發專案中，處理表格數據與 Excel 匯出是常見的需求。 最近在專案中，遇到一個常見但讓人頭痛的錯誤訊息： Uncaught ReferenceError: numericFields is not defined 本文將用簡單的方式，帶你了解這個錯誤的成因、如何修改程式碼，以及如何最佳化「合併儲存格」與「數值加總」的邏輯。 問題場景：表格合併與數值加總 專案中需要達成以下功能： 同一個 APS_PLAN_NO 下，若 TFT4 相同，則 TFT5 欄位要合併。 數值欄位要加總，並以千分位格式輸出。 匯出到 Excel 時，也要套用相同邏輯。 在 Vue 的 rowSpans 計算邏輯中，初始版本如下： const rowSpans = computed ( () => { const rows = filteredData. value const spans = rows. map ( () => { const obj = {} tableHeaders. value . forEach ( key => obj[key] = 0 ) return obj }) ... return spans }) 但是，當我們在 exportToExcel() 使用時，出現了以下錯誤： Uncaught ReferenceError: numericFields is not defined 錯誤原因 這個錯誤訊息代表： 👉 numericFields 在程式中根本沒有宣告，所以瀏覽器不知道它是什麼。 舉例來說，假設我們在程式裡寫了這樣的判斷： if (numericFields. includes (key)) { // 處理數值欄位 } 但其實 numericFields 沒有定義，程式就會直接報錯。 解決方案：定義 numericFields ...
閱讀完整內容

🖥️遠端桌面連線完整新手指南：Windows RDP、Chrome Remote Desktop、AnyDesk、TeamViewer 一次搞懂

-
目錄 什麼是遠端桌面？我為什麼需要？ 方法一：Windows 10/11 內建遠端桌面（RDP） 方法二：Chrome Remote Desktop（免固定 IP） 方法三：AnyDesk / TeamViewer（跨平台、穿透 NAT） 安全性最佳實務 常見問題與故障排除（速查） 工具比較表 選擇流程圖（工程師的懶人決策） 出門前最後檢查清單 FAQ 常見問答 什麼是遠端桌面？我為什麼需要？ 「遠端桌面連線」讓你在 A 電腦上，直接操作 B 電腦的桌面與程式，就像坐在 B 電腦前一樣。 常見情境：出差連回公司電腦、在家存取辦公室檔案、協助同事排錯、或在手機/平板臨時控管主機。 方法一：Windows 10/11 內建遠端桌面（RDP） 前置條件 被連線的電腦版本需為 Windows 10/11 專業版或企業版。 兩台電腦彼此能網路互通（同網段、VPN、或正確路由）。 被連線端設定（只需做一次） 打開： 設定 → 系統 → 遠端桌面 → 啟用。 記下「此電腦名稱」或 IP。 確保 Windows 防火牆允許「遠端桌面」。 連線端操作 按 Win + R → 輸入 mstsc → Enter。 在「電腦」欄位輸入對方電腦名稱或 IP → 連線 → 輸入該電腦的帳號密碼。 跨外網的正確姿勢 首選 VPN ：先連公司/家裡的 VPN，再用 RDP，安全又穩。 不建議直接把 3389 埠對外曝光；若不得已，請做好強密碼、改連接埠、IP 白名單與入侵偵測。 方法二：Chrome Remote Desktop（免固定 IP） 優點是跨平台、穿透 NAT、用 Google 帳號配對，對新手友善。 在被連線與連線端都安裝 Chrome 與 Chrome Remote Desktop 擴充。 以同一 Google 帳號登入，為「被連線」電腦設定 PIN。...
閱讀完整內容

🔎EF Core 連 Oracle 出現 ORA-00600 [kpp_concatq:2] 的完整排錯指南（含 EF Core ToString/CultureInfo 錯誤）

-
  摘要（Meta Description） 在 .NET EF Core 對 Oracle 查詢時，若遭遇 ORA-00600 [kpp_concatq:2] ，多半是查詢優化器在字串型別轉換或重寫（Query Transformation）時踩到版本 Bug。本文以實戰日誌形式，示範如何用 FromSqlRaw + 參數型別/長度對齊 + CAST + Hint 立即止血，並處理 EF Core 另一個常見錯誤：「 CultureInfo 常值被傳進 ToString 造成可能記憶體外洩 」。附可直接套用的程式碼。 目錄 錯誤現象 根因分析 三步驟「立即止血」 安全重構：可直接貼用的程式碼 進階避雷：CTE/MATERIALIZE 與 View 包裝 EF Core ToString / CultureInfo 錯誤的正確處理 版本與長期解法（根治） 效能與穩定性小技巧 檢查清單（Checklist） FAQ 結語 錯誤現象 常見堆疊片段如下（節錄）： Oracle .ManagedDataAccess .Client .OracleException ( 0 x80004005): ORA-00600 : 內部錯誤代碼, 引數: [kpp_concatq:2] , [4] , [3] ...
閱讀完整內容