SQL Server 預存程序移植至 MySQL 實戰範例：排序調整邏輯

背景

專案需將 SQL Server (T-SQL) 預存程序移植至 MySQL。本文以「項目上移/下移排序」邏輯為例，展示語法差異對照與移植要點。

原始 T-SQL (SQL Server)

CREATE PROCEDURE ChangeSequence
    @_sign INT,           -- 0=上移, 1=下移
    @TableName VARCHAR(50),
    @ItemName VARCHAR(50),   -- PK 欄位
    @ItemID INT,
    @SortName VARCHAR(50),   -- 排序欄位
    @TypeName VARCHAR(50),   -- 分類欄位
    @TypeValue VARCHAR(50)   -- 分類值
AS
BEGIN
    -- 建立暫存表儲存該分類下所有項目及排序值
    CREATE TABLE #Tab (_ItemID INT, _Sort INT);
    INSERT #Tab SELECT @ItemName, @SortName FROM @TableName
    WHERE @TypeName = @TypeValue ORDER BY _Sort;

    -- 取得當前項目排序值、前後鄰項 ID
    DECLARE @ThisSort INT, @PrevID INT, @NextID INT, @Count INT;
    SELECT @Count = COUNT(*) FROM #Tab;
    SELECT @ThisSort = _Sort FROM #Tab WHERE _ItemID = @ItemID;
    IF @ThisSort > 1 SELECT @PrevID = _ItemID FROM #Tab WHERE _Sort = @ThisSort - 1;
    IF @ThisSort < @Count SELECT @NextID = _ItemID FROM #Tab WHERE _Sort = @ThisSort + 1;

    -- 交換排序值
    IF @_sign = 0 AND @ThisSort > 1
        UPDATE @TableName SET @SortName = @ThisSort-1 WHERE @ItemName = @ItemID,
        UPDATE @TableName SET @SortName = @ThisSort   WHERE @ItemName = @PrevID;
    ELSE IF @_sign = 1 AND @ThisSort < @Count
        UPDATE @TableName SET @SortName = @ThisSort+1 WHERE @ItemName = @ItemID,
        UPDATE @TableName SET @SortName = @ThisSort   WHERE @ItemName = @NextID;
END

移植後 MySQL 版本

DELIMITER $$
DROP PROCEDURE IF EXISTS ChangeSequence$$
CREATE PROCEDURE ChangeSequence(
    _sign INT,            -- 0=上移, 1=下移
    TableName VARCHAR(50),
    ItemName VARCHAR(50),
    ItemID INT,
    SortName VARCHAR(50),
    TypeName VARCHAR(50),
    TypeValue VARCHAR(50)
)
BEGIN
    DECLARE _SQL VARCHAR(4000);
    DECLARE ThisSort INT;
    DECLARE PrevID INT;
    DECLARE NextID INT;
    DECLARE _Count INT;

    -- MySQL 臨時表需顯式 CREATE TEMPORARY TABLE
    CREATE TEMPORARY TABLE _Tab (
        _ItemID INT,
        _Sort INT
    );

    -- 動態 SQL 建構 INSERT (MySQL 用 PREPARE/EXECUTE)
    SET _SQL = CONCAT('INSERT INTO _Tab (_ItemID,_Sort) SELECT ', ItemName, ',', SortName, ' FROM ', TableName);
    IF (TypeName <> '' AND TypeValue <> '') THEN
        SET _SQL = CONCAT(_SQL, ' WHERE ', TypeName, '=', TypeValue);
    END IF;
    SET _SQL = CONCAT(_SQL, ' ORDER BY _Sort ASC');
    SET @v_sql = _SQL;
    PREPARE stmt FROM @v_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET _SQL = '';

    -- 取得統計與鄰項
    SELECT COUNT(*) INTO _Count FROM _Tab;
    SELECT _Sort INTO ThisSort FROM _Tab WHERE _ItemID = ItemID;
    IF (ThisSort > 1) THEN
        SELECT _ItemID INTO PrevID FROM _Tab WHERE _Sort = (ThisSort - 1);
    END IF;
    IF (ThisSort < _Count) THEN
        SELECT _ItemID INTO NextID FROM _Tab WHERE _Sort = (ThisSort + 1);
    END IF;

    -- 建構並執行 UPDATE
    IF (_sign = 0) THEN
        IF (ThisSort > 1) THEN
            SET _SQL = CONCAT('UPDATE ', TableName, ' SET ', SortName, '=', CONVERT((ThisSort-1), CHAR),
                              ' WHERE ', ItemName, '=', CONVERT(ItemID, CHAR), ';',
                              'UPDATE ', TableName, ' SET ', SortName, '=', CONVERT(ThisSort, CHAR),
                              ' WHERE ', ItemName, '=', CONVERT(PrevID, CHAR));
        END IF;
    ELSE
        IF (ThisSort < _Count) THEN
            SET _SQL = CONCAT('UPDATE ', TableName, ' SET ', SortName, '=', CONVERT((ThisSort+1), CHAR),
                              ' WHERE ', ItemName, '=', CONVERT(ItemID, CHAR), ';',
                              'UPDATE ', TableName, ' SET ', SortName, '=', CONVERT(ThisSort, CHAR),
                              ' WHERE ', ItemName, '=', CONVERT(NextID, CHAR));
        END IF;
    END IF;

    SET @v_sql1 = _SQL;
    PREPARE stmt1 FROM @v_sql1;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;

關鍵差異對照表

移植檢核清單

1. 將所有 @var → var。
2. #Temp → TEMPORARY TABLE。
3. EXEC(@sql) → PREPARE/EXECUTE/DEALLOCATE。
4. + 串接 → CONCAT()。
5. IF BEGIN END → IF THEN END IF; (注意分號)。
6. 保留字/關鍵字加反引號 ` (如 `order`)。
7. 測試動態 SQL 拼接後的最終語句 (用 SELECT @v_sql 除錯)。