儲存程序

在 SQL Server 中撰寫函式與預存程序

Meghan Kwartler

IT Consultant

什麼是儲存程序？

是什麼？

常式會：

接受輸入參數
執行動作（EXECUTE、SELECT、INSERT、UPDATE、DELETE，及其他 SP 陳述式）
回傳狀態（成功或失敗）
回傳輸出參數

為何使用儲存程序？

為什麼用？

可縮短執行時間
可減少網路流量
支援模組化程式設計
強化安全性

有何差異？

UDF

必須回傳值
- 可為資料表值
允許內嵌 SELECT 執行
不支援輸出參數
不可 INSERT、UPDATE、DELETE
不可執行 SP
無錯誤處理

回傳值可選
- 不支援資料表值
不可內嵌在 SELECT 中執行
可回傳輸出參數與狀態
可 INSERT、UPDATE、DELETE
可執行函式與 SP
以 TRY...CATCH 處理錯誤

含 OUTPUT 參數的 CREATE PROCEDURE

-- 前四行程式碼
-- SP 名稱必須唯一
CREATE PROCEDURE dbo.cuspGetRideHrsOneDay 
    @DateParm date,
    @RideHrsOut numeric OUTPUT
AS
.......

含 OUTPUT 參數的 CREATE PROCEDURE

CREATE PROCEDURE dbo.cuspGetRideHrsOneDay
    @DateParm date,
    @RideHrsOut numeric OUTPUT
AS
SET NOCOUNT ON
BEGIN
SELECT 
  @RideHrsOut = SUM(
    DATEDIFF(second, PickupDate, DropoffDate)
  )/ 3600
FROM YellowTripData
WHERE CONVERT(date, PickupDate) = @DateParm
RETURN
END;

輸出參數 vs. 傳回值

輸出參數

可為任何資料型別
每個 SP 可宣告多個
不可為資料表值參數

傳回值

用於表示成功或失敗
僅限整數型別
0 表示成功，非 0 表示失敗

你已準備好使用 CREATE PROCEDURE！

在 SQL Server 中撰寫函式與預存程序