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!

Lukasz Szozda · Accepted Answer · 2022-02-09 19:48:55Z

5

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:

edited Feb 9, 2022 at 19:48

answered Feb 9, 2022 at 19:43

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Laura Beranek Over a year ago

Thank you very much! This is exactly what I need! It works great.

Laura Beranek Over a year ago

Would you be able to show me an example of using dynamic SQL to determine if a role exists? For each schema in this code above, I need to create a role if it does not exist. If it does, then I want to continue to the next schema. Let me know if I should post this as a new question.

Lukasz Szozda Over a year ago

@LauraBeranek You could building query CREATE ROLE IF NOT EXISTS name_of_schema.. inside the FOR loop . The query will not try to overwrite existing role. If your scenario is more roboust I suggest asking a more detailed question

Laura Beranek Over a year ago

Thank you! Yes, I am already doing that. But creating the role is just one of many things it needs to do. If the role exists, then all of that has been done and I want to skip it altogether. Some of the things I need to do will fail if tried more than once (ie granting future grants). I will pose a new question. Thank you!

Lukasz Szozda Over a year ago

@LauraBeranek Please find attached example of conditional statement in Snowflake Scripting: stackoverflow.com/a/70918783/5070879 It could be placed inside loop and multiple steps could be done using this pattern

Collectives™ on Stack Overflow

Dynamic SQL in a Snowflake SQL Stored Procedure

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related