SQL Server 2005 建立簡單預存程序完整指南：語法、參數、游標、執行範例

基本結構

CREATE PROCEDURE my_pro
    @inputDate VARCHAR(20),           -- 輸入參數
    @Result VARCHAR(255) OUTPUT       -- 輸出參數
AS
BEGIN
    DECLARE @variable1 VARCHAR(255);
    DECLARE @variable2 INT;

    IF ... BEGIN ... END
    ELSE BEGIN ... END
END;
GO

刪除預存程序

DROP PROCEDURE my_pro;

執行方式

-- 1. 無參數
EXEC my_pro;

-- 2. 僅輸入參數
EXEC my_pro '2023-01-01';

-- 3. 有輸出參數
DECLARE @Result VARCHAR(1024);
EXEC my_pro '2023-01-01', @Result OUTPUT;
SELECT @Result AS Result;

實戰範例：游標合併多列為單一字串

需求：Test 表中 Test01 > 10000 的列，將 Test02 以逗號合併回傳。

CREATE PROCEDURE my_Cursor
    @id INT,
    @Result VARCHAR(255) OUTPUT
AS
BEGIN
    DECLARE city_cursor CURSOR FOR
        SELECT Test01 FROM Test WHERE Test01 > @id;

    SET @Result = '';
    DECLARE @Field INT;

    OPEN city_cursor;
    FETCH NEXT FROM city_cursor INTO @Field;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @Result = ''
            SELECT @Result = Test02 FROM Test WHERE Test01 = @Field;
        ELSE
            SELECT @Result = @Result + ',' + Test02 FROM Test WHERE Test01 = @Field;

        FETCH NEXT FROM city_cursor INTO @Field;
    END

    CLOSE city_cursor;
    DEALLOCATE city_cursor;
END;
GO

-- 執行
DECLARE @Result VARCHAR(1024);
EXEC my_Cursor 10000, @Result OUTPUT;
SELECT @Result AS Result;

三大身分識別函數補充

實用中繼資料查詢

SELECT IDENT_CURRENT('TableName');           -- 目前身分值
SELECT IDENT_INCR('TableName');              -- 增量
SELECT IDENT_SEED('TableName');              -- 種子
SELECT IDENT_CURRENT('TableName') + IDENT_INCR('TableName'); -- 下一個值

其他資料庫對應

• MySQL：LAST_INSERT_ID()
• Oracle：RETURNING id INTO :v / Sequence
• PostgreSQL：INSERT ... RETURNING id

最佳實務提醒

• 預設使用 SCOPE_IDENTITY() 取得新增 ID。
• 游標效能較差，能用集合操作 (XML PATH, STRING_AGG) 盡量避免。
• 輸出參數務必加 OUTPUT 關鍵字，否預設為輸入。
• 預存程序命名建議加前綴 (如 usp_) 區分系統物件。