Is there print command in stored proc in snowflake

0
votes

I have a proc in dynamic sql throwing an error. I need to see the print statement of the sql the proc is running. The error message from snowflake is not very helpful.

Here is the stored proc I have 

CREATE OR REPLACE PROCEDURE util.dbo.Restore_db_ver("source_db" varchar, "restore_db" varchar, "dest_db" varchar)
returns float
LANGUAGE JAVASCRIPT
AS
$$
    var perm_cmd = "select cp_object,cp_object_name,cp_permission_type, cp_permission,cp_role from util.dbo.restore_perm where cp_database_name = '"+restore_db+"'"
    var perm_cmd_stmt = snowflake.createStatement({sqlText: perm_cmd})
    var perm_record = perm_cmd_stmt.execute()
    while(perm_record.next())
    {var perm_object = perm_record.getColumnValue(1)
     var perm_object_name = perm_record.getColumnValue(2)
     var perm_permission_type = perm_record.getColumnValue(3)
     var perm_permission_name = perm_record.getColumnValue(4)
     var perm_role_name = perm_record.getColumnValue(5)    


    var sql_cmd1 = "CREATE OR REPLACE DATABASE "+restore_db+" CLONE "+source_db+" AT (OFFSET => -1800)"
    var statement1 = snowflake.createStatement( {sqlText: sql_cmd1} )
    var result_set1 = statement1.execute()

    var sql_cmd2 = perm_permission_type+" "+perm_permission_name+" ON "+perm_object+" "+perm_object_name+" TO ROLE "+perm_role_name
    var statement2 = snowflake.createStatement( {sqlText: sql_cmd2} )
    var result_set2 = statement2.execute()
    }

$$

This is throwing an error JavaScript execution error: Uncaught ReferenceError: permission_name is not defined in RESTORE_DB_VER at ' var sql_cmd2 = perm_permission_type+" "+permission_name+" ON "+perm_object+" "+perm_object_name+" TO ROLE "+perm_role_name' position 44 stackstrace: RESTORE_DB_VER line: 17

Is there print command that I can use to output the sql?

2
snowflake-cloud-data-platform

2 Answers

0
votes

Several of your lines do not end in a semicolon, including the one immediately before your variable definition for permission_name. Without a semicolon, it looks like one code line to JavaScript, so permission_name never gets defined as a variable. You should also not re-define variable inside of the loop.

I can't test the code without the data, but it should look more like this:

CREATE OR REPLACE PROCEDURE util.dbo.Restore_db_ver("source_db" varchar, "restore_db" varchar, "dest_db" varchar)
returns float
LANGUAGE JAVASCRIPT
AS
$$
    var perm_object, perm_object_name, perm_permission_type, perm_permission_name, perm_role_name, sql_cmd1, sql_cmd2, statement1, statement2
        result_set1, result_set2;
    var perm_cmd = "select cp_object,cp_object_name,cp_permission_type, cp_permission,cp_role from util.dbo.restore_perm where cp_database_name = '"+restore_db+"'";
    var perm_cmd_stmt = snowflake.createStatement({sqlText: perm_cmd});
    var perm_record = perm_cmd_stmt.execute();
    while(perm_record.next()){
        perm_object = perm_record.getColumnValue(1);
        perm_object_name = perm_record.getColumnValue(2);
        perm_permission_type = perm_record.getColumnValue(3);
        perm_permission_name = perm_record.getColumnValue(4);
        perm_role_name = perm_record.getColumnValue(5);    

        sql_cmd1 = "CREATE OR REPLACE DATABASE "+restore_db+" CLONE "+source_db+" AT (OFFSET => -1800)";
        statement1 = snowflake.createStatement( {sqlText: sql_cmd1} );
        result_set1 = statement1.execute();

        sql_cmd2 = perm_permission_type+" "+perm_permission_name+" ON "+perm_object+" "+perm_object_name+" TO ROLE "+perm_role_name;
        statement2 = snowflake.createStatement( {sqlText: sql_cmd2} );
        result_set2 = statement2.execute();
    }

$$
0
votes

Option1:
To see the SQL command executed before running, you could so something like this.

CREATE OR REPLACE PROCEDURE util.dbo.Restore_db_ver("source_db" varchar, "restore_db" varchar, "dest_db" varchar)
returns string
LANGUAGE JAVASCRIPT
AS
$$
    var perm_cmd = "select cp_object,cp_object_name,cp_permission_type, cp_permission,cp_role from util.dbo.restore_perm where cp_database_name = '"+restore_db+"'";

return perm_cmd ;
$$
call <procedure> < params> ;
SELECT *
FROM TABLE(result_scan(last_query_id()));

Option2: To see what's executed you can utilize snowflake query tag and query history. 

-- before executing the procedure
ALTER session SET QUERY_TAG = <procedure tag>;

Filter the query history with the procedure tag to list all SQL commands executed.