{"id":298,"date":"2020-02-01T00:46:07","date_gmt":"2020-02-01T00:46:07","guid":{"rendered":"https:\/\/snowflake.pavlik.us\/?p=298"},"modified":"2020-02-01T00:47:19","modified_gmt":"2020-02-01T00:47:19","slug":"executing-multiple-sql-statements-in-a-stored-procedure-part-deux","status":"publish","type":"post","link":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/02\/01\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\/","title":{"rendered":"Executing Multiple SQL Statements in a Stored Procedure &#8211; Part Deux"},"content":{"rendered":"\n<p>A customer requested the ability to <a href=\"https:\/\/snowflake.pavlik.us\/index.php\/2019\/08\/22\/executing-multiple-sql-statements-in-a-stored-procedure\/\">execute multiple SQL statements that result from a query<\/a>. 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?<\/p>\n\n\n\n<p>This updated stored procedure handles generated SQL statements that may encounter an error. You have two options to handle errors &#8212; report errors and continue or report first error and stop.<\/p>\n\n\n\n<p>The stored procedure is overloaded, meaning that you can call it with or without the second parameter &#8220;continueOnError&#8221;. If you do not supply the second parameter, it will default to false and stop after the first error.<\/p>\n\n\n\n<p>The output of the stored procedure is as follows:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n&lt;SQL statement to run&gt; --Succeeded\n&lt;SQL statement to run&gt; --Failed: &lt;reason for failure&gt;\n<\/pre><\/div>\n\n\n<p>By indicating the success or failure status as a SQL comment, you can modify and re-run the line manually or do some troubleshooting.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nuse database TEST;\nuse warehouse TEST;\n \ncreate or replace procedure RunBatchSQL(sqlCommand String)\n    returns string\n    language JavaScript\nas\n$$\n\/**\n * Stored procedure to execute multiple SQL statements generated from a SQL query\n * Note that this procedure will always use the column named &quot;SQL_COMMAND&quot;\n * This overload of the function will stop after encountering the first error.\n *\n * @param {String} sqlCommand: The SQL query to run to generate one or more SQL commands \n * @return {String}: A string containing all the SQL commands executed, each separated by a newline. \n *\/\n      cmd1_dict = {sqlText: SQLCOMMAND};\n      stmt = snowflake.createStatement(cmd1_dict);\n      rs = stmt.execute();\n      var s = &#039;&#039;;\n      var CONTINUEONERROR = false; \/\/ Default to false for overloaded function\n      while (rs.next()) {\n          try{\n                cmd2_dict = {sqlText: rs.getColumnValue(&quot;SQL_COMMAND&quot;)};\n                stmtEx = snowflake.createStatement(cmd2_dict);\n                stmtEx.execute();\n                s += rs.getColumnValue(1) + &quot; --Succeeded&quot; + &quot;\\n&quot;;\n             }\n          catch(err) {\n                s += rs.getColumnValue(1) + &quot; --Failed: &quot; + err.message.replace(\/\\n\/g, &quot; &quot;) + &quot;\\n&quot;;\n                if (!CONTINUEONERROR) return s;\n          }\n      }\n      return s;\n$$;\n \ncreate or replace procedure RunBatchSQL(sqlCommand String, continueOnError Boolean)\n    returns string\n    language JavaScript\nas\n$$\n\/**\n * Stored procedure to execute multiple SQL statements generated from a SQL query\n * Note that this procedure will always use the column named &quot;SQL_COMMAND&quot;.\n * This overload of the function will continue on errors if &quot;continueOnError&quot; = true.\n *\n * @param {String} sqlCommand: The SQL query to run to generate one or more SQL commands \n * @param {Boolean} continueOnError: If true, continues on error. If false, stops after first error.\n * @return {String}: A string containing all the SQL commands executed, each separated by a newline. \n *\/\n      cmd1_dict = {sqlText: SQLCOMMAND};\n      stmt = snowflake.createStatement(cmd1_dict);\n      rs = stmt.execute();\n      var s = &#039;&#039;;\n      while (rs.next()) {\n          try{\n                cmd2_dict = {sqlText: rs.getColumnValue(&quot;SQL_COMMAND&quot;)};\n                stmtEx = snowflake.createStatement(cmd2_dict);\n                stmtEx.execute();\n                s += rs.getColumnValue(1) + &quot; --Succeeded&quot; + &quot;\\n&quot;;\n             }\n          catch(err) {\n                s += rs.getColumnValue(1) + &quot; --Failed: &quot; + err.message.replace(\/\\n\/g, &quot; &quot;) + &quot;\\n&quot;;\n                if (!CONTINUEONERROR) return s;\n          }\n      }\n      return s;\n$$\n;\n\n-- This is a select query that will generate a list of SQL commands to excute, in this case some grant statements. \n-- This SQL will generate rows to grant select on all tables for the DBA role (change to specify another role). \nselect distinct (&#039;grant select on table &#039; || table_schema || &#039;.&#039; || table_name || &#039; to role DBA;&#039;) AS SQL_COMMAND\nfrom INFORMATION_SCHEMA.TABLE_PRIVILEGES\nwhere TABLE_SCHEMA &lt;&gt; &#039;AUDIT&#039;\norder by SQL_COMMAND;\n \n-- As a convienience, this grabs the last SQL run so that it&#039;s easier to insert into the parameter used to call the stored procedure. \nset query_text = (  select QUERY_TEXT\n                    from table(information_schema.query_history(result_limit =&gt; 2))\n                    where SESSION_ID = Current_Session() and QUERY_TYPE = &#039;SELECT&#039; order by START_TIME desc);\n \n-- Confirm that the query_text variable has the correct SQL query to generate our SQL commands (grants in this case) to run.\nselect $query_text as QUERY_TEXT;\n \n-- Run the stored procedure. Note that to view its output better, double click on the output to see it in multi-line format,\nCall RunBatchSQL($query_text, true);\n \n--Check the last several queries run to make sure it worked.\nselect QUERY_TEXT\nfrom table(information_schema.query_history(result_limit =&gt; 100))\nwhere SESSION_ID = Current_Session() order by START_TIME desc;\n\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,13],"tags":[],"class_list":["post-298","post","type-post","status-publish","format-standard","hentry","category-sql","category-stored-procedures-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\r\n<title>Executing Multiple SQL Statements in a Stored Procedure - Part Deux - Snowflake in the Carolinas<\/title>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/snowflake.pavlik.us\/index.php\/2020\/02\/01\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Executing Multiple SQL Statements in a Stored Procedure - Part Deux - Snowflake in the Carolinas\" \/>\r\n<meta property=\"og:description\" content=\"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 [&hellip;]\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/snowflake.pavlik.us\/index.php\/2020\/02\/01\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\/\" \/>\r\n<meta property=\"og:site_name\" content=\"Snowflake in the Carolinas\" \/>\r\n<meta property=\"article:published_time\" content=\"2020-02-01T00:46:07+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2020-02-01T00:47:19+00:00\" \/>\r\n<meta name=\"author\" content=\"Greg Pavlik\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Greg Pavlik\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/02\\\/01\\\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/02\\\/01\\\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\\\/\"},\"author\":{\"name\":\"Greg Pavlik\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#\\\/schema\\\/person\\\/019455f4675665b6cf5edea31ec44d7b\"},\"headline\":\"Executing Multiple SQL Statements in a Stored Procedure &#8211; Part Deux\",\"datePublished\":\"2020-02-01T00:46:07+00:00\",\"dateModified\":\"2020-02-01T00:47:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/02\\\/01\\\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\\\/\"},\"wordCount\":156,\"commentCount\":0,\"articleSection\":[\"SnowSQL\",\"Stored Procedures\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/02\\\/01\\\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/02\\\/01\\\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\\\/\",\"url\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/02\\\/01\\\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\\\/\",\"name\":\"Executing Multiple SQL Statements in a Stored Procedure - Part Deux - Snowflake in the Carolinas\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#website\"},\"datePublished\":\"2020-02-01T00:46:07+00:00\",\"dateModified\":\"2020-02-01T00:47:19+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#\\\/schema\\\/person\\\/019455f4675665b6cf5edea31ec44d7b\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/02\\\/01\\\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/02\\\/01\\\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/02\\\/01\\\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/snowflake.pavlik.us\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Executing Multiple SQL Statements in a Stored Procedure &#8211; Part Deux\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#website\",\"url\":\"https:\\\/\\\/snowflake.pavlik.us\\\/\",\"name\":\"Snowflake in the Carolinas\",\"description\":\"Random thoughts on all things Snowflake in the Carolinas\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/snowflake.pavlik.us\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#\\\/schema\\\/person\\\/019455f4675665b6cf5edea31ec44d7b\",\"name\":\"Greg Pavlik\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g\",\"caption\":\"Greg Pavlik\"},\"description\":\"Greg is a Senior Sales Engineer at Snowflake Computing, in the Raleigh-Durham area. He's been in data management and security for the twenty years.\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Executing Multiple SQL Statements in a Stored Procedure - Part Deux - Snowflake in the Carolinas","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/02\/01\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\/","og_locale":"en_US","og_type":"article","og_title":"Executing Multiple SQL Statements in a Stored Procedure - Part Deux - Snowflake in the Carolinas","og_description":"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 [&hellip;]","og_url":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/02\/01\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\/","og_site_name":"Snowflake in the Carolinas","article_published_time":"2020-02-01T00:46:07+00:00","article_modified_time":"2020-02-01T00:47:19+00:00","author":"Greg Pavlik","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Greg Pavlik","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/02\/01\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\/#article","isPartOf":{"@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/02\/01\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\/"},"author":{"name":"Greg Pavlik","@id":"https:\/\/snowflake.pavlik.us\/#\/schema\/person\/019455f4675665b6cf5edea31ec44d7b"},"headline":"Executing Multiple SQL Statements in a Stored Procedure &#8211; Part Deux","datePublished":"2020-02-01T00:46:07+00:00","dateModified":"2020-02-01T00:47:19+00:00","mainEntityOfPage":{"@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/02\/01\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\/"},"wordCount":156,"commentCount":0,"articleSection":["SnowSQL","Stored Procedures"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/snowflake.pavlik.us\/index.php\/2020\/02\/01\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/02\/01\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\/","url":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/02\/01\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\/","name":"Executing Multiple SQL Statements in a Stored Procedure - Part Deux - Snowflake in the Carolinas","isPartOf":{"@id":"https:\/\/snowflake.pavlik.us\/#website"},"datePublished":"2020-02-01T00:46:07+00:00","dateModified":"2020-02-01T00:47:19+00:00","author":{"@id":"https:\/\/snowflake.pavlik.us\/#\/schema\/person\/019455f4675665b6cf5edea31ec44d7b"},"breadcrumb":{"@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/02\/01\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/snowflake.pavlik.us\/index.php\/2020\/02\/01\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/02\/01\/executing-multiple-sql-statements-in-a-stored-procedure-part-deux\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/snowflake.pavlik.us\/"},{"@type":"ListItem","position":2,"name":"Executing Multiple SQL Statements in a Stored Procedure &#8211; Part Deux"}]},{"@type":"WebSite","@id":"https:\/\/snowflake.pavlik.us\/#website","url":"https:\/\/snowflake.pavlik.us\/","name":"Snowflake in the Carolinas","description":"Random thoughts on all things Snowflake in the Carolinas","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/snowflake.pavlik.us\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/snowflake.pavlik.us\/#\/schema\/person\/019455f4675665b6cf5edea31ec44d7b","name":"Greg Pavlik","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g","caption":"Greg Pavlik"},"description":"Greg is a Senior Sales Engineer at Snowflake Computing, in the Raleigh-Durham area. He's been in data management and security for the twenty years."}]}},"_links":{"self":[{"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/posts\/298","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/comments?post=298"}],"version-history":[{"count":2,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/posts\/298\/revisions"}],"predecessor-version":[{"id":300,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/posts\/298\/revisions\/300"}],"wp:attachment":[{"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/media?parent=298"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/categories?post=298"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/tags?post=298"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}