如何将SQL Server存储过程转换为Snowflake兼容版本？解决FROM表名问题

需要将以下SQL Server存储过程转换为Snowflake兼容的版本，原存储过程实现了对state.DatabaseExtract表的Upsert操作、按需返回关联查询结果并提交事务。转换时遇到的问题是Snowflake不支持原SQL中FROM语句后的子查询别名写法，现需要完成转换。

原SQL Server存储过程：

CREATE OR ALTER PROCEDURE [state].[AddUpdateDatabaseExtractStatus]
    @databaseId         INT,
    @databaseLoadId     INT,
    @dataFactoryRunId   CHAR(36),
    @phase              VARCHAR(30),
    @startTimeUtc       DATETIME2(7),
    @endTimeUtc         DATETIME2(7),
    @loadGroup          INT = 1,
    @statusId           INT = 1,
    @returnResult       BIT = 1
AS
BEGIN

DECLARE @lv_databaseLoadId INT;

IF @lv_databaseLoadId IS NULL
BEGIN
    -- Upsert the row
    MERGE [state].[DatabaseExtract] AS [OE]
    USING (
        VALUES (
            @databaseId,
            @DatabaseLoadId,
            @phase,
            @dataFactoryRunId,
            @startTimeUtc,
            @endTimeUtc,
            @loadGroup,
            @statusId
        )
    ) AS [NEW] (
        [DatabaseId]
        ,[DatabaseLoadId]
        ,[Phase]
        ,[DataFactoryRunId]
        ,[StartTimeUtc]
        ,[EndTimeUtc]
        ,[LoadGroup]
        ,[StatusId]
    )
    ON [OE].[DatabaseId] = [NEW].[DatabaseId] AND [OE].[DatabaseLoadId] = [NEW].[DatabaseLoadId]
    -- AND [OE].[DataFactoryRunId] = [NEW].[DataFactoryRunId]
    WHEN MATCHED THEN
       UPDATE SET
          [StatusId] = [NEW].[StatusId],
          [Phase] = [NEW].[Phase],
          [EndTimeUtc] = [NEW].[EndTimeUtc]
    WHEN NOT MATCHED
    -- Add defense again out of band runs causing issues - simply skip logging if the data is not present.
        AND @StartTimeUTC IS NOT NULL AND @loadGroup IS NOT NULL AND @Phase IS NOT NULL and @dataFactoryRunId IS NOT NULL AND @statusId IS NOT NULL
        THEN
        INSERT
        (
            [DatabaseId]
            ,[LoadGroup]
            ,[StatusId]
            ,[Phase]
            ,[DataFactoryRunId]
            ,[StartTimeUtc]
            ,[EndTimeUtc]
        ) VALUES (
            [NEW].[DatabaseId]
            ,[NEW].[LoadGroup]
            ,[NEW].[StatusId]
            ,[NEW].[Phase]
            ,[NEW].[DataFactoryRunId]
            ,[NEW].[StartTimeUtc]
            ,[NEW].[EndTimeUtc]
        )
    ;

    IF @returnResult = 1
        SELECT
            DE.[DatabaseLoadId],
            [OperatorName],
            [ServerName],
            [DatabaseName],
            [SourceSystemName],
            [KeyVaultSecretName],
            --[BatchProcess],
            [DNACoreTechSupportEmail]
        FROM (
            SELECT
                MAX([DatabaseLoadId]) AS DatabaseLoadId,
                DatabaseId
            FROM [state].[DatabaseExtract]
            WHERE [DataFactoryRunId] = @dataFactoryRunId AND DatabaseId = @databaseId
            GROUP BY DatabaseId
        ) DE
        INNER JOIN [state_config].[DatabaseList] DL ON DL.[DatabaseId] = DE.[DatabaseId]
END

if ((@@trancount) > 0)
COMMIT TRANSACTION -- Safe for other loads to lookup IDs now

END
GO

转换后的Snowflake存储过程 #

CREATE OR REPLACE PROCEDURE state.AddUpdateDatabaseExtractStatus(
    databaseId INT,
    databaseLoadId INT,
    dataFactoryRunId CHAR(36),
    phase VARCHAR(30),
    startTimeUtc TIMESTAMP_NTZ(7),
    endTimeUtc TIMESTAMP_NTZ(7),
    loadGroup INT DEFAULT 1,
    statusId INT DEFAULT 1,
    returnResult BOOLEAN DEFAULT TRUE
)
RETURNS TABLE (
    DatabaseLoadId INT,
    OperatorName VARCHAR,
    ServerName VARCHAR,
    DatabaseName VARCHAR,
    SourceSystemName VARCHAR,
    KeyVaultSecretName VARCHAR,
    DNACoreTechSupportEmail VARCHAR
)
LANGUAGE SQL
EXECUTE AS CALLER
AS $$
DECLARE
    lv_databaseLoadId INT;
BEGIN
    IF lv_databaseLoadId IS NULL THEN
        -- Upsert操作：适配Snowflake MERGE语法
        MERGE INTO state.DatabaseExtract AS OE
        USING (
            SELECT
                :databaseId AS DatabaseId,
                :databaseLoadId AS DatabaseLoadId,
                :phase AS Phase,
                :dataFactoryRunId AS DataFactoryRunId,
                :startTimeUtc AS StartTimeUtc,
                :endTimeUtc AS EndTimeUtc,
                :loadGroup AS LoadGroup,
                :statusId AS StatusId
        ) AS NEW
        ON OE.DatabaseId = NEW.DatabaseId AND OE.DatabaseLoadId = NEW.DatabaseLoadId
        WHEN MATCHED THEN
            UPDATE SET
                StatusId = NEW.StatusId,
                Phase = NEW.Phase,
                EndTimeUtc = NEW.EndTimeUtc
        WHEN NOT MATCHED
            AND :startTimeUtc IS NOT NULL 
            AND :loadGroup IS NOT NULL 
            AND :phase IS NOT NULL 
            AND :dataFactoryRunId IS NOT NULL 
            AND :statusId IS NOT NULL
        THEN
            INSERT (
                DatabaseId,
                LoadGroup,
                StatusId,
                Phase,
                DataFactoryRunId,
                StartTimeUtc,
                EndTimeUtc
            ) VALUES (
                NEW.DatabaseId,
                NEW.LoadGroup,
                NEW.StatusId,
                NEW.Phase,
                NEW.DataFactoryRunId,
                NEW.StartTimeUtc,
                NEW.EndTimeUtc
            );

        IF returnResult THEN
            -- Snowflake完全支持子查询别名写法，修正参数引用方式即可
            RETURN TABLE (
                SELECT
                    DE.DatabaseLoadId,
                    DL.OperatorName,
                    DL.ServerName,
                    DL.DatabaseName,
                    DL.SourceSystemName,
                    DL.KeyVaultSecretName,
                    DL.DNACoreTechSupportEmail
                FROM (
                    SELECT
                        MAX(DatabaseLoadId) AS DatabaseLoadId,
                        DatabaseId
                    FROM state.DatabaseExtract
                    WHERE DataFactoryRunId = :dataFactoryRunId AND DatabaseId = :databaseId
                    GROUP BY DatabaseId
                ) DE
                INNER JOIN state_config.DatabaseList DL ON DL.DatabaseId = DE.DatabaseId
            );
        END IF;
    END IF;

    -- 适配Snowflake事务处理逻辑
    IF (CURRENT_TRANSACTION() IS NOT NULL) THEN
        COMMIT;
    END IF;
END;
$$;

关键转换说明 #

• 参数定义：Snowflake存储过程参数无需@前缀，默认值通过DEFAULT指定；将SQL Server的BIT类型替换为Snowflake的BOOLEAN，DATETIME2(7)替换为TIMESTAMP_NTZ(7)以保证时间精度一致。
• MERGE语法：Snowflake使用MERGE INTO替代SQL Server的MERGE，USING子句改用SELECT语句构造数据源（更适配Snowflake语法），存储过程参数引用需添加:前缀。
• 子查询兼容：Snowflake完全支持FROM子句后使用带别名的子查询，原问题大概率是参数未加:导致的语法错误，转换后已修正参数引用方式。
• 事务处理：Snowflake默认自动提交事务，判断事务是否存在改用CURRENT_TRANSACTION()函数，替代SQL Server的@@trancount全局变量。
• 返回结果：Snowflake存储过程如需返回表结构结果，需在定义时声明RETURNS TABLE，并通过RETURN TABLE()包裹查询语句返回结果集。

内容的提问来源于stack exchange，提问作者Mohamed Sharif