Snowflake Stored Procedure Use Statement

Question

I am working with a Snowflake stored procedure that will take a view that is changed from one database to another one. I am trying

  var sqlCommand = `
    SELECT a.OBJECT_NAME, a.OBJECT_SCHEMA, a.OBJECT_TYPE, d.VIEW_DEFINITION
    FROM VIEW_OBJECT_LIST a
      INNER JOIN DB_DEV.INFORMATION_SCHEMA.VIEWS d ON a.OBJECT_NAME = d.TABLE_NAME AND a.OBJECT_SCHEMA = d.TABLE_SCHEMA
      INNER JOIN DB_QA.INFORMATION_SCHEMA.VIEWS q ON a.OBJECT_NAME = q.TABLE_NAME AND a.OBJECT_SCHEMA = q.TABLE_SCHEMA AND d.VIEW_DEFINITION != q.VIEW_DEFINITION;`;
      
  var viewList = snowflake.createStatement({ sqlText: sqlCommand}).execute();
  
  while(viewList.next()){
    var sql = viewList.VIEW_DEFINITION;
    var sql = 'USE DB_QA; ' || sql;
    snowflake.createStatement({ sqlText: sql }).execute();
  }

but getting the error message

Unsupported statement type 'USE'. At Statement.execute

Is there a way in a Snowflake stored procedure to be able to run a USE statement as part of a call to the Snowflake API?

Answer 1

I believe the only issue is that you have not defined what DB_QA is in your USE statement. I assume its the name of a database, so you just need to modify that line to be:

var sql = 'USE DATABASE DB_QA; ' || sql

I am not sure whether snowflake.createStatement allows for a multi-statement query to be passed in, though. You'll have to let me know if that works.

Answer 2

what is the execute permission you are using while creating this stored procedure? If you are using "EXECUTE AS OWNER" in the create procedure statement then you might not be able to use the statements other than below statements :->

Restrictions on SQL Statements
Although caller’s rights stored procedures can execute any SQL statement that the caller has sufficient privileges to execute outside a stored procedure, owner’s rights stored procedures can call only a subset of SQL statements.

The following SQL statements can be called from inside an owner’s rights stored procedure:

SELECT.

DML.

DDL. (See above for restrictions on the ALTER USER statement.)

GRANT/REVOKE.

Variable assignment.

DESCRIBE and SHOW. (See limitations documented above.)

Other SQL statements cannot be called from inside an owner’s rights stored procedure.

Please try creating same procedure using "EXECUTE AS CALLER" this allows snowflake native sql commands like LIST, USE DATABASE,RM, etc.