如何将参数传入Snowflake存储过程的RESULTSET并生成视图DDL

需要编写一个接收4个参数（src_database、src_schema、target_database、target_schema）的Snowflake存储过程，用于为指定库和schema下的所有表生成视图DDL。使用硬编码值时功能正常，但替换为参数后抛出错误invalid identifier 'SRC_SCHEMA'。

现有可正常运行的无参数版本：

CREATE OR REPLACE PROCEDURE create_views ()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  res VARCHAR DEFAULT '';
BEGIN
  LET res_set RESULTSET := (SELECT DISTINCT table_name, 
                        LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY ordinal_position) as nm_column
                        FROM INFORMATION_SCHEMA.COLUMNS
                        WHERE table_schema = 'DEMO_SCHEMA'
                        AND table_catalog = 'DEMO_DB'
                        AND table_name = 'AFF_REL'
                        group by table_name);
LET table_cursor CURSOR FOR res_set;
  FOR var in table_cursor DO
    res := 'CREATE OR REPLACE VIEW ' || var.table_name || ' AS SELECT ' || var.nm_column || ' FROM ' || var.table_name;
  END FOR;
CLOSE table_cursor;

RETURN res;
END;
$$;

目标是将src_database.src_schema.var.table_name作为源表，target_database.target_schema.var.table_name作为目标视图生成DDL，但带参数的版本始终报错。

在Snowflake SQL存储过程中，直接在静态SQL里引用存储过程参数会被解析为列名，导致invalid identifier错误，必须通过绑定变量或带参数的游标来正确传递参数。以下是两种可行的实现方案：

方案1：带参数的游标（简洁高效） #

声明带参数的游标，打开时传入存储过程的参数值：

CREATE OR REPLACE PROCEDURE create_views(src_database STRING, src_schema STRING, target_database STRING, target_schema STRING)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  res VARCHAR DEFAULT '';
  -- 定义带参数的游标
  table_cursor CURSOR (p_db STRING, p_schema STRING) FOR 
    SELECT DISTINCT table_name, 
           LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY ordinal_position) AS nm_column
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_catalog = p_db
      AND table_schema = p_schema
    GROUP BY table_name;
BEGIN
  -- 打开游标并绑定参数
  OPEN table_cursor USING (:src_database, :src_schema);
  
  FOR var IN table_cursor DO
    -- 拼接完整的视图DDL，包含目标库、schema和源表全路径
    res := res || 'CREATE OR REPLACE VIEW ' || target_database || '.' || target_schema || '.' || var.table_name || 
           ' AS SELECT ' || var.nm_column || ' FROM ' || src_database || '.' || src_schema || '.' || var.table_name || ';' || CHAR(10);
  END FOR;
  
  CLOSE table_cursor;
  RETURN res;
END;
$$;

方案2：动态SQL（灵活适配复杂场景） #

通过动态SQL构建查询语句，使用绑定变量传递参数，再将结果存入结果集：

CREATE OR REPLACE PROCEDURE create_views(src_database STRING, src_schema STRING, target_database STRING, target_schema STRING)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  res VARCHAR DEFAULT '';
  res_set RESULTSET;
  table_cursor CURSOR FOR res_set;
  sql_query STRING;
BEGIN
  -- 构建带占位符的动态SQL
  sql_query := 'SELECT DISTINCT table_name, 
                   LISTAGG(column_name, '', '') WITHIN GROUP (ORDER BY ordinal_position) AS nm_column
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE table_catalog = ?
                  AND table_schema = ?
                GROUP BY table_name';
  
  -- 执行动态SQL并将结果存入结果集，同时绑定参数
  EXECUTE IMMEDIATE :sql_query INTO res_set USING (:src_database, :src_schema);
  
  FOR var IN table_cursor DO
    -- 拼接完整DDL语句
    res := res || 'CREATE OR REPLACE VIEW ' || target_database || '.' || target_schema || '.' || var.table_name || 
           ' AS SELECT ' || var.nm_column || ' FROM ' || src_database || '.' || src_schema || '.' || var.table_name || ';' || CHAR(10);
  END FOR;
  
  RETURN res;
END;
$$;

核心注意点 #

• 存储过程参数不能直接嵌入静态SELECT语句，必须通过绑定变量或带参游标传递，否则Snowflake会将参数识别为列名，抛出标识符错误。
• 拼接DDL时必须指定完整的数据库和schema路径，避免依赖当前会话的默认库/上下文，保证语句的通用性。
• 两种方案都能解决问题，方案1更适合简单查询场景，方案2适用于需要动态调整查询逻辑的复杂需求。

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