CODEWITHSUNDEEP
snowflake-cloud-data-platform
Shajeer
Shajeer

Reputation: 11

USE DATABASE in stored procedure

I am trying to set my database inside the stored procedure, but getting errors, Any advice? Thanks in advance

Below code return error "Unsupported statement type 'USE'."

CREATE  PROCEDURE PROC_TEST()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    try{
    var my_sql_command = "USE DATABASE EDWH_DEV";
          var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
          statement1.execute();
          result = "Succeeded";
          }
      catch (err)  {
          result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
          result += "\n  Message: " + err.message;
          result += "\nStack Trace:\n" + err.stackTraceTxt; 
          }
      return result;
$$;

CALL PROC_TEST();

Upvotes: 1

Views: 2251

Answers (1)

Sriga
Sriga

Reputation: 1321

You need to include line, On which privilege the procedure need to execute:

Include line: execute as caller It will work

CREATE  or replace PROCEDURE PROC_TEST()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
execute as caller
AS
$$
    try{
    var my_sql_command = "USE DATABASE GCP";
          var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
          statement1.execute();
          result = "Succeeded";
          }
      catch (err)  {
          result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
          result += "\n  Message: " + err.message;
          result += "\nStack Trace:\n" + err.stackTraceTxt; 
          }
      return result;
$$;

Upvotes: 3

Related Questions