如何在Snowflake SQL存储过程中抛出含变量的异常？

现有Snowflake SQL存储过程MAIN.PUBLIC.PROPERTY_CONSISTENCY_CHECK，需要实现以下需求：

• 将检查出的错误信息（error_messages变量）嵌入异常输出，替换原异常信息中的HIEREINFUEGEN
• 移除成功时的字符串返回逻辑，仅在出现错误时抛出异常
• 在Jenkins流水线中调用该存储过程时，若触发异常则立即终止流水线，并在错误日志中显示完整的错误信息

一、修改Snowflake存储过程 #

调整存储过程的异常抛出逻辑，动态拼接错误信息，并移除成功时的字符串返回：

CREATE OR REPLACE PROCEDURE MAIN.PUBLIC.PROPERTY_CONSISTENCY_CHECK()
RETURNS NULL  -- 改为返回NULL，不再返回字符串
LANGUAGE SQL
AS
$$
DECLARE
    variable_1 INTEGER;
    variable_2 STRING;
    variable_3 DATE;
    variable_4 STRING;
    error_messages STRING := ''; 

BEGIN
    -- 原有检查逻辑保持不变
    SELECT COUNT(*) 
    INTO variable_1 
    FROM TABLE1;

    SELECT LISTAGG(Property_ID, ', ') 
    INTO variable_2
    FROM (
        SELECT DISTINCT Property_ID 
        FROM TABLE1
        AT (TIMESTAMP => '2024-11-19 07:00:00'::TIMESTAMP)
        GROUP BY Customer_ID, Property_ID 
        HAVING COUNT(*) > 1
    );

    SELECT DISTINCT UPDATED_AT 
    INTO variable_3 
    FROM TABLE1 
    AT (TIMESTAMP => '2024-11-19 07:00:00'::TIMESTAMP);

    SELECT LISTAGG(Property_ID, ', ') 
    INTO variable_4
    FROM TABLE2 dim
    WHERE dim.Property_ID NOT IN (
        SELECT DISTINCT Property_ID 
        FROM TABLE1
    );

    -- 错误信息拼接逻辑保持不变
    IF (variable_1 = 0) THEN
        error_messages := error_messages || 'The Customer Property Table is empty;' || CHR(10) || CHR(10);  
    END IF;

    IF (variable_2 IS NOT NULL AND variable_2 != '') THEN
        error_messages := error_messages || 'Duplicate Property detected: ' || variable_2 || ';' || CHR(10) || CHR(10);  
    END IF;

    IF (variable_3 IS NULL OR variable_3 != CURRENT_DATE()) THEN
        error_messages := error_messages || 'The Updated_AT Column is not the Current Date;' || CHR(10) || CHR(10);
    END IF;

    IF (variable_4 IS NOT NULL AND variable_4 != '') THEN
        error_messages := error_messages || 'Missing Properties detected: ' || variable_4 || ';' || CHR(10) || CHR(10);  
    END IF;

    -- 动态抛出包含错误信息的异常
    IF (error_messages != '') THEN
        RAISE EXCEPTION(-20002, 'Following errors occurred: ' || error_messages);  
    ELSE
        RETURN NULL;  -- 无错误时返回NULL，替代原字符串返回
    END IF;

END;
$$;

关键修改点： #

• 将RETURNS STRING改为RETURNS NULL，满足“不返回字符串”的要求
• 移除固定自定义异常定义，改用RAISE EXCEPTION动态生成包含error_messages的异常内容
• 成功分支返回NULL，替代原有的成功提示字符串

二、Jenkins流水线配置 #

在Jenkins流水线中调用存储过程时，需捕获异常并终止流水线，以下是两种常见实现方式：

方式1：使用Snowflake CLI调用 #

假设Jenkins节点已配置Snowflake CLI并完成认证：

pipeline {
    agent any
    stages {
        stage('Run Consistency Check') {
            steps {
                script {
                    try {
                        sh 'snow sql -q "CALL MAIN.PUBLIC.PROPERTY_CONSISTENCY_CHECK();"'
                    } catch (Exception e) {
                        error "Property consistency check failed with errors: ${e.getMessage()}"
                    }
                }
            }
        }
    }
}

方式2：使用自定义脚本调用 #

如果用Python/Java等自定义脚本调用Snowflake，需确保脚本在捕获到异常时以非零退出码结束，Jenkins会自动终止流水线：

pipeline {
    agent any
    stages {
        stage('Run Consistency Check') {
            steps {
                sh 'python3 run_consistency_check.py'
            }
        }
    }
}

关键配置说明： #

• 通过try-catch块捕获CLI调用的异常，或依赖自定义脚本的退出码来识别错误
• 调用error方法终止流水线，并将异常信息输出到Jenkins日志，确保错误内容可查

内容的提问来源于stack exchange，提问作者codejüngling