I'm trying to write a stored procedure that would take 4 parameters and generate a DDL for each table found in snowflake's metadata. I've managed to loop through teh resultset with hardcoded values instead of parameters. However it's not yet functional, as I need to be able to pass src_database, src_schema, target_database, target_schema as parameters.

This is what I have so far:

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;
$$;

What I'm aiming to do is to pass this src_database.src_schema.var.table_name into CREATE OR REPLACE VIEW and target_database.target_schema.var.table_name.

When I try to exchange the hardcoded values for parameters, it throws an error invalid identifier 'SRC_SCHEMA'

The procedure with parameters looks like this:

CREATE OR REPLACE PROCEDURE create_views (src_database STRING, src_schema STRING)
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 = src_schema
                        AND table_catalog = src_database
                        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;
$$;

I'v etried this as well:

                        WHERE table_schema = ?
                        AND table_catalog = ?
                        group by table_name);
OPEN res_set USING (:src_database, :src_schema);

How can I pass these parameters into my queries?

I'm trying to write a stored procedure that would take 4 parameters and generate a DDL for each table found in snowflake's metadata. I've managed to loop through teh resultset with hardcoded values instead of parameters. However it's not yet functional, as I need to be able to pass src_database, src_schema, target_database, target_schema as parameters.

This is what I have so far:

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;
$$;

What I'm aiming to do is to pass this src_database.src_schema.var.table_name into CREATE OR REPLACE VIEW and target_database.target_schema.var.table_name.

When I try to exchange the hardcoded values for parameters, it throws an error invalid identifier 'SRC_SCHEMA'

The procedure with parameters looks like this:

CREATE OR REPLACE PROCEDURE create_views (src_database STRING, src_schema STRING)
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 = src_schema
                        AND table_catalog = src_database
                        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;
$$;

I'v etried this as well:

                        WHERE table_schema = ?
                        AND table_catalog = ?
                        group by table_name);
OPEN res_set USING (:src_database, :src_schema);

How can I pass these parameters into my queries?

• stored-procedures
• snowflake-cloud-data-platform

1 Answer 1

In order to use parameter/variable in SQL inside scripting block you have to prefix it with colon:

CREATE OR REPLACE PROCEDURE create_views (src_database STRING, src_schema STRING)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  res VARCHAR DEFAULT '';
BEGIN
  LET res_set RESULTSET := (
    SELECT table_name, 
      LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY ordinal_position) as nm_column
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = :src_schema
      AND table_catalog = :src_database
    GROUP BY table_name);
  -- rest of the code 

   RETURN res;
END;
$$