CODEWITHSUNDEEP
javascriptsnowflake-cloud-data-platformsnowflake-schemajava-stored-procedures
Nanda
Nanda

Reputation: 189

Stored Procedure - Snowflake - get details of all columns and rows

I am trying to get 4 column names from the table which has 10 rows and I want to loop through all rows in that table and pass those column values as parameters into some other stored procedure.

How can I store the result set of the below query and use them to pass in a for loop ? CREATE OR REPLACE PROCEDURE SP_MAIN() RETURNS VARCHAR(16777216) LANGUAGE JAVASCRIPT EXECUTE AS CALLER AS '

var DETS = select * from SNAPSHOT.SNAPSHOT_CONFIG ; snowflake.execute({sqlText: DETS});

'

;

Upvotes: 0

Views: 762

Answers (1)

Nanda
Nanda

Reputation: 189

I was able to acheive this as below:

CREATE or replace PROCEDURE SNAPSHOPT_SP()
RETURNS VARCHAR
LANGUAGE javascript
AS
$$

//Array created to store parameters. 
var SRC_DATABASE = [];
var SRC_SCHEMA = [];
var TRGT_DATABASE = [];
var SRC_TABLE = [];
var TRGT_TABLE = [];




// create for the following tables
var rs = snowflake.execute( { sqlText: 
` select * from SNAPSHOT.SNAPSHOT_CONFIG `} );

//load values from table into Array,  we will be looping through the array to execute the store proc

while (rs.next()){
    var SRC_DATABASE_NAME = rs.getColumnValue(1);
    SRC_DATABASE.push(SRC_DATABASE_NAME);
    
    var SRC_SCHEMA_NAME = rs.getColumnValue(2);
    SRC_SCHEMA.push(SRC_SCHEMA_NAME);
    
    var TRGT_SCHEMA_NAME = rs.getColumnValue(3);
    TRGT_DATABASE.push(TRGT_SCHEMA_NAME);
    
    var SRC_TABLE_NAME = rs.getColumnValue(4);
    SRC_TABLE.push(SRC_TABLE_NAME);
    
    var TRGT_TABLE_NAME = rs.getColumnValue(5);
    TRGT_TABLE.push(TRGT_TABLE_NAME);
    
                  }
//run store proc for each table name - format for store proc = SP_SNAPSHOT(SRC_DATABASE_NAME, SRC_SCHEMA_NAME, TRGT_SCHEMA_NAME, SRC_TABLE_NAME , TRGT_TABLE_NAME);                  

for (var i = 0; i < SRC_TABLE.length; i++) {
        snowflake.execute( { sqlText: "CALL SP_SNAPSHOT(:1,:2,:3,:4,:5) ;", binds: [SRC_DATABASE[i],SRC_SCHEMA[i],TRGT_DATABASE[i],SRC_TABLE[i],TRGT_TABLE[i]] });
       
        }
$$;

CALL SNAPSHOPT_SP();

Upvotes: 0

Related Questions