SQL Server 2008 預存程序與函數開發完整範例：參數、動態條件查詢、游標、觸發器、表值函數

本文彙整 SQL Server 2008 預存程序與使用者定義函數的核心語法與實戰範例，涵蓋輸入/輸出參數、純量函數、內聯/多陳述式表值函數、避免動態 SQL 的多條件查詢模式、變數賦值、臨時表/表變數、迴圈、條件判斷、游標、觸發器 (Inserted/Deleted) 等完整開發工具箱。

一、預存程序基礎範例

1. 僅輸入參數

CREATE PROC GetComment @commentid INT
AS
SELECT * FROM Comment WHERE CommentID = @commentid;

2. 輸入 + 輸出參數

CREATE PROC GetCommentCount @newsid INT, @count INT OUTPUT
AS
SELECT @count = COUNT(*) FROM Comment WHERE NewsID = @newsid;

3. 純量函數 (回傳單一值)

CREATE FUNCTION MyFunction (@newsid INT) RETURNS INT
AS
BEGIN
    DECLARE @count INT;
    SELECT @count = COUNT(*) FROM Comment WHERE NewsID = @newsid;
    RETURN @count;
END;

-- 呼叫
DECLARE @cnt INT;
EXEC @cnt = MyFunction 2;
PRINT @cnt;

4. 內聯表值函數 (單一 SELECT 回傳 TABLE)

CREATE FUNCTION GetFunctionTable (@newsid INT) RETURNS TABLE
AS RETURN (SELECT * FROM Comment WHERE NewsID = @newsid);

-- 呼叫
SELECT * FROM GetFunctionTable(2);

5. 多陳述式表值函數 (彈性邏輯填充 TABLE 變數)

CREATE FUNCTION FUNC_UserTab_2 (@myId INT) RETURNS @t TABLE (
    ID INT NOT NULL, Oid INT NOT NULL, Login NVARCHAR(50) NOT NULL,
    Rtx NVARCHAR(4) NOT NULL, Name NVARCHAR(5) NOT NULL,
    Password NVARCHAR(MAX) NULL, State NVARCHAR(8) NOT NULL
)
AS
BEGIN
    INSERT INTO @t SELECT * FROM ST_User WHERE ID < @myId;
    RETURN;
END;

二、多條件查詢：避免動態 SQL 拼接字串的三種寫法

場景：依 @addDate、@name 選擇性篩選。

寫法 1：分支明確 (程式碼較冗長)

IF @addDate IS NOT NULL AND @name <> ''
    SELECT * FROM table WHERE addDate = @addDate AND name = @name;
ELSE IF @addDate IS NOT NULL AND @name = ''
    SELECT * FROM table WHERE addDate = @addDate;
ELSE IF @addDate IS NULL AND @name <> ''
    SELECT * FROM table WHERE name = @name;
ELSE
    SELECT * FROM table;

寫法 2：WHERE 條件短路評估 (推薦，簡潔高效)

SELECT * FROM table
WHERE (addDate = @addDate OR @addDate IS NULL)
  AND (name = @name OR @name = '');

寫法 3：CASE 運算式 (適合動態欄位比對)

SELECT * FROM table
WHERE addDate = CASE WHEN @addDate IS NULL THEN addDate ELSE @addDate END
  AND name = CASE WHEN @name = '' THEN name ELSE @name END;

三、T-SQL 程式設計基礎元素

1. 變數宣告與賦值

-- 簡單 SET
DECLARE @a INT; SET @a = 5; PRINT @a;

-- SELECT 賦值 (單一值)
DECLARE @user1 NVARCHAR(50);
SELECT @user1 = '張三'; PRINT @user1;

-- 從資料表查詢賦值
DECLARE @user2 NVARCHAR(50);
SELECT @user2 = Name FROM ST_User WHERE ID = 1; PRINT @user2;

-- UPDATE 子句賦值 (較少用)
DECLARE @user3 NVARCHAR(50);
UPDATE ST_User SET @user3 = Name WHERE ID = 1; PRINT @user3;

2. 臨時表 (#) 與表變數 (@)

-- 建立臨時表
CREATE TABLE #DU_User1 (ID INT, Oid INT, Login NVARCHAR(50), Rtx NVARCHAR(4), Name NVARCHAR(5), Password NVARCHAR(MAX), State NVARCHAR(8));

-- INSERT INTO 臨時表
INSERT INTO #DU_User1 VALUES (100,2,'LS','0000','臨時','321','特殊');

-- SELECT INTO 快速建立並填充
SELECT * INTO #DU_User2 FROM ST_User WHERE ID < 8;

-- UNION 合併
SELECT * FROM #DU_User2 WHERE ID < 3 UNION SELECT * FROM #DU_User1;

-- 清理
DROP TABLE #DU_User1; DROP TABLE #DU_User2;

-- 加入身分識別欄位
ALTER TABLE #t ADD myid INT IDENTITY(1,1);
ALTER TABLE #t ADD myid1 UNIQUEIDENTIFIER DEFAULT NEWID();

-- 查詢結果加流水號 (無主鍵)
SELECT IDENTITY(INT,1,1) AS ID, Name, Login, Password INTO #t FROM ST_User;

-- 表變數
DECLARE @t TABLE (id INT NOT NULL, msg NVARCHAR(50) NULL);
INSERT INTO @t VALUES (1,'1'), (2,'2');
SELECT * FROM @t;

3. WHILE 迴圈

DECLARE @i INT = 1, @sum INT = 0;
WHILE @i <= 100
BEGIN
    SET @sum += @i;
    SET @i += 1;
END;
PRINT @sum; -- 5050

4. 條件判斷 IF/ELSE 與 CASE

IF (1+1=2) PRINT '對' ELSE PRINT '錯';

DECLARE @today INT = 3, @week NVARCHAR(10);
SET @week = CASE @today
    WHEN 1 THEN N'星期一' WHEN 2 THEN N'星期二' WHEN 3 THEN N'星期三'
    WHEN 4 THEN N'星期四' WHEN 5 THEN N'星期五' WHEN 6 THEN N'星期六'
    WHEN 7 THEN N'星期日' ELSE N'值錯誤' END;
PRINT @week;

5. 游標

DECLARE @ID INT, @Oid INT, @Login VARCHAR(50);
DECLARE user_cur CURSOR FOR SELECT ID, Oid, Login FROM ST_User;
OPEN user_cur;
FETCH NEXT FROM user_cur INTO @ID, @Oid, @Login;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @ID;
    FETCH NEXT FROM user_cur INTO @ID, @Oid, @Login;
END;
CLOSE user_cur;
DEALLOCATE user_cur;

6. 觸發器 (DML AFTER)

CREATE TRIGGER User_OnUpdate ON ST_User FOR UPDATE
AS
DECLARE @msg NVARCHAR(100);
SELECT @msg = N'姓名從「' + d.Name + N'」修改為「' + i.Name + N'」'
FROM Inserted i JOIN Deleted d ON i.ID = d.ID;
INSERT INTO [LOG](MSG) VALUES (@msg);
GO

-- 移除
DROP TRIGGER User_OnUpdate;

Inserted：INSERT/UPDATE 後的新資料列。
Deleted：DELETE/UPDATE 前的舊資料列。

7. 帶 OUTPUT 參數與 RETURN 值的預存程序

-- OUTPUT 參數
CREATE PROC PR_Sum @a INT, @b INT, @sum INT OUTPUT
AS SET @sum = @a + @b;

-- RETURN 值 (僅限 INT)
CREATE PROC PR_Sum2 @a INT, @b INT
AS RETURN @a + @b;

-- 呼叫 OUTPUT
DECLARE @mysum INT; EXEC PR_Sum 1,2,@mysum OUTPUT; PRINT @mysum;

-- 呼叫 RETURN
DECLARE @mysum2 INT; EXEC @mysum2 = PR_Sum2 1,2; PRINT @mysum2;

四、函數分類總覽

五、開發最佳實務提醒

• **優先使用內聯表值函數** 替代多陳述式，優化器可展開邏輯產生更好計畫。
• **避免游標**，改用集合式操作 (JOIN, APPLY, 窗格函數)；游標僅用於維護腳本/一次性資料修正。
• **觸發器保持輕量**，避免觸發器中發送郵件、呼叫 Web Service 等耗時操作；複雜邏輯改用 Service Broker/非同步處理。
• **預存程序參數化** 確保計畫重用，防止 SQL 注入。
• **臨時表 vs 表變數**：臨時表支援統計資訊/索引，適合大量資料；表變數編譯輕量，適合小資料集/函數回傳。

掌握上述語法與模式，即可高效開發健壯、可維護的 SQL Server 2008 預存程序與函數模組。