Snowflake存储过程查找文本值二次运行报错：临时表不存在？

Snowflake的SQL存储过程默认是原子执行——只要过程中任何一步出错，所有操作（包括创建临时表、插入数据）都会被回滚。你第二次运行时，处理某张表/列的动态SQL执行失败，导致整个过程回滚，临时表discovery_results根本没被创建（或被回滚删除），所以后续SELECT会报错“对象不存在”。

另外，你直接拼接字符串到动态SQL里，不仅有SQL注入风险，还可能因为表名/列名含特殊字符（比如空格、引号）导致语法错误，这也是第二次运行出错的潜在诱因。

修改存储过程，关闭原子性、添加错误处理、改用绑定变量，确保单步失败不终止整个过程，同时避免语法问题：

修改后的存储过程代码 #

CREATE OR REPLACE PROCEDURE find_text_occurrences(target_text STRING)
RETURNS STRING
LANGUAGE SQL
AUTOMATIC_COMMIT = TRUE -- 关闭原子性，单步操作独立提交，失败不回滚已完成操作
AS
$$
DECLARE
    table_name STRING;
    column_name STRING;
    all_columns CURSOR FOR (
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns
        WHERE table_schema NOT IN ('INFORMATION_SCHEMA', 'YOUR_EXCLUDED_SCHEMA') 
        AND data_type = 'TEXT'
    );
    sql_stmt STRING;
    error_msg STRING;
BEGIN
    -- 创建或重建临时表，新增错误列记录失败信息
    CREATE OR REPLACE TEMPORARY TABLE discovery_results (
        table_name STRING,
        column_name STRING,
        matches INT,
        error STRING DEFAULT NULL
    );

    -- 遍历所有目标列
    FOR record IN all_columns DO
        table_name := record.table_schema || '.' || record.table_name;
        column_name := record.column_name;
        
        -- 用绑定变量+IDENTIFIER函数构建安全的动态SQL
        sql_stmt := 'INSERT INTO discovery_results (table_name, column_name, matches) ' ||
                    'SELECT :1, :2, COUNT(*) ' ||
                    'FROM ' || IDENTIFIER(:table_name) || ' ' ||
                    'WHERE ' || IDENTIFIER(:column_name) || ' LIKE ''%'' || :3 || ''%''';
        
        BEGIN
            EXECUTE IMMEDIATE sql_stmt USING table_name, column_name, target_text;
        EXCEPTION
            WHEN OTHERS THEN
                error_msg := SQLERRM;
                -- 记录失败的表/列及错误信息
                INSERT INTO discovery_results (table_name, column_name, error)
                VALUES (table_name, column_name, error_msg);
        END;
    END FOR;

    RETURN '结果已存入discovery_results表，执行SELECT * FROM discovery_results查看详情（含失败记录）';
END;
$$;

调用方式 #

不用每次修改存储过程，直接传入目标文本即可：

-- 查找"Alternative Credential"
CALL find_text_occurrences('Alternative Credential');

-- 查找"Marketing"
CALL find_text_occurrences('Marketing');

-- 查看结果
SELECT * FROM discovery_results;

关键优化点 #

• AUTOMATIC_COMMIT = TRUE：让存储过程中每个操作独立提交，某步失败时，已完成的结果不会被回滚
• IDENTIFIER()：处理含特殊字符的表名/列名，避免语法错误
• 绑定变量：替代直接字符串拼接，杜绝SQL注入，同时避免单引号转义问题
• 错误捕获：记录处理失败的表/列信息，方便排查问题

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