A customer requested the ability to execute multiple SQL statements that result from a query. Today I learned about a new use case that required some augmentation of the stored procedure. Specifically, what happens when one of the many generated SQL statements encounters an error?
This updated stored procedure handles generated SQL statements that may encounter an error. You have two options to handle errors — report errors and continue or report first error and stop.
The stored procedure is overloaded, meaning that you can call it with or without the second parameter “continueOnError”. If you do not supply the second parameter, it will default to false and stop after the first error.
The output of the stored procedure is as follows:
<SQL statement to run> --Succeeded
<SQL statement to run> --Failed: <reason for failure>
By indicating the success or failure status as a SQL comment, you can modify and re-run the line manually or do some troubleshooting.
use database TEST;
use warehouse TEST;
create or replace procedure RunBatchSQL(sqlCommand String)
returns string
language JavaScript
as
$$
/**
* Stored procedure to execute multiple SQL statements generated from a SQL query
* Note that this procedure will always use the column named "SQL_COMMAND"
* This overload of the function will stop after encountering the first error.
*
* @param {String} sqlCommand: The SQL query to run to generate one or more SQL commands
* @return {String}: A string containing all the SQL commands executed, each separated by a newline.
*/
cmd1_dict = {sqlText: SQLCOMMAND};
stmt = snowflake.createStatement(cmd1_dict);
rs = stmt.execute();
var s = '';
var CONTINUEONERROR = false; // Default to false for overloaded function
while (rs.next()) {
try{
cmd2_dict = {sqlText: rs.getColumnValue("SQL_COMMAND")};
stmtEx = snowflake.createStatement(cmd2_dict);
stmtEx.execute();
s += rs.getColumnValue(1) + " --Succeeded" + "\n";
}
catch(err) {
s += rs.getColumnValue(1) + " --Failed: " + err.message.replace(/\n/g, " ") + "\n";
if (!CONTINUEONERROR) return s;
}
}
return s;
$$;
create or replace procedure RunBatchSQL(sqlCommand String, continueOnError Boolean)
returns string
language JavaScript
as
$$
/**
* Stored procedure to execute multiple SQL statements generated from a SQL query
* Note that this procedure will always use the column named "SQL_COMMAND".
* This overload of the function will continue on errors if "continueOnError" = true.
*
* @param {String} sqlCommand: The SQL query to run to generate one or more SQL commands
* @param {Boolean} continueOnError: If true, continues on error. If false, stops after first error.
* @return {String}: A string containing all the SQL commands executed, each separated by a newline.
*/
cmd1_dict = {sqlText: SQLCOMMAND};
stmt = snowflake.createStatement(cmd1_dict);
rs = stmt.execute();
var s = '';
while (rs.next()) {
try{
cmd2_dict = {sqlText: rs.getColumnValue("SQL_COMMAND")};
stmtEx = snowflake.createStatement(cmd2_dict);
stmtEx.execute();
s += rs.getColumnValue(1) + " --Succeeded" + "\n";
}
catch(err) {
s += rs.getColumnValue(1) + " --Failed: " + err.message.replace(/\n/g, " ") + "\n";
if (!CONTINUEONERROR) return s;
}
}
return s;
$$
;
-- This is a select query that will generate a list of SQL commands to excute, in this case some grant statements.
-- This SQL will generate rows to grant select on all tables for the DBA role (change to specify another role).
select distinct ('grant select on table ' || table_schema || '.' || table_name || ' to role DBA;') AS SQL_COMMAND
from INFORMATION_SCHEMA.TABLE_PRIVILEGES
where TABLE_SCHEMA <> 'AUDIT'
order by SQL_COMMAND;
-- As a convienience, this grabs the last SQL run so that it's easier to insert into the parameter used to call the stored procedure.
set query_text = ( select QUERY_TEXT
from table(information_schema.query_history(result_limit => 2))
where SESSION_ID = Current_Session() and QUERY_TYPE = 'SELECT' order by START_TIME desc);
-- Confirm that the query_text variable has the correct SQL query to generate our SQL commands (grants in this case) to run.
select $query_text as QUERY_TEXT;
-- Run the stored procedure. Note that to view its output better, double click on the output to see it in multi-line format,
Call RunBatchSQL($query_text, true);
--Check the last several queries run to make sure it worked.
select QUERY_TEXT
from table(information_schema.query_history(result_limit => 100))
where SESSION_ID = Current_Session() order by START_TIME desc;