Dynamic SQL in a Snowflake SQL Stored Procedure

Question

I would like to run a select query in a Snowflake SQL Stored Procedures (not Javascript) using dynamic SQL. Is this possible?

What I am looking to do is pass in a database name as a parameter. I then want to select all schema names from INFORMATION_SCHEMA.SCHEMATA for that database. I would like to use the input parameter value for the database name to fully qualify the table. For example, DBNAME.INFORMATION_SCHEMA.SCHEMATA where DBNAME is the name of my input parameter.

I would then like to loop through the results and for each schema do some code.

I find lots of Dynamic SQL examples for Javascript but I can't find one for a SQL stored procedure (language SQL) because it is so new. Please let me know if this is possible and an example if possible. Thanks!

user_5070879 · Answer

An example of commenting schemas in a specific database:

Preparing setup:

CREATE DATABASE TEST3;
CREATE SCHEMA TEST1;
CREATE SCHEMA TEST2;

SELECT CATALOG_NAME, SCHEMA_NAME, COMMENT
FROM TEST3.INFORMATION_SCHEMA.SCHEMATA;

Procedure:

CREATE OR REPLACE PROCEDURE test_proc(DB_NAME STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
   TABLE_NAME   STRING;
   QUERY        STRING;
   OUTPUT       STRING DEFAULT '';
   c1           CURSOR FOR SELECT SCHEMA_NAME FROM TABLE(?) 
                WHERE SCHEMA_NAME != 'INFORMATION_SCHEMA';
BEGIN
   TABLE_NAME := CONCAT(DB_NAME, '.INFORMATION_SCHEMA.SCHEMATA');
   OPEN c1 USING (TABLE_NAME);

   FOR rec IN c1 DO
       QUERY := 'COMMENT ON SCHEMA ' || DB_NAME || '.' || rec.SCHEMA_NAME
                 || ' IS ''test_comment'';';
       OUTPUT := OUTPUT || QUERY;
       EXECUTE IMMEDIATE :QUERY;
   END FOR;
   
   RETURN :OUTPUT;
END;
$$;

Call:

CALL test_proc('TEST3');

Output:

Dynamic SQL in a Snowflake SQL Stored Procedure

1 Answers