CODEWITHSUNDEEP
snowflake-cloud-data-platform
GODS Gift
GODS Gift

Reputation: 1

how to run simple select query and get reusult in snowflakes procedure?

how to run simple select query and get reusult in snowflakes procedure?

CREATE OR REPLACE PROCEDURE COARSDB0P_DB.COUNT_MISMATCH() 
RETURNS VARCHAR  
LANGUAGE JAVASCRIPT  
AS  
$$  
var command ="SELECT b.TABLE_CATALOG,b.TABLE_SCHEMA,b.TABLE_NAME,b.TABLE_TYPE,b.ROW_COUNT as Dev_COUNT,a.ROW_COUNT as UAT_COUNT FROM TABLE A ,information_schema.tables B where b.TABLE_NAME=a.TABLE_NAMES and b.TABLE_SCHEMA=a.TABLE_SCHEMA and b.TABLE_TYPE=a.TABLE_TYPE and TRY_CAST(b.ROW_COUNT as NUMBER) != TRY_CAST(a.ROW_COUNT as NUMBER);"
var cmd1_dict = {sqlText: command};  
var stmt = snowflake.createStatement(cmd1_dict);  
var rs = stmt.execute();
rs.next();
var output = rs.getColumnValue();
  return output;
$$;

I need to return the actualy output of mentioned SLECT query.

Upvotes: 0

Views: 72

Answers (2)

Dave Welden
Dave Welden

Reputation: 1918

See Returning tabular data from a stored procedure. Note this is for a SQL stored procedure, not Javascript. You will want something like:

create procedure ...
returns table (catalog varchar, schema varchar, ...)
declare
   query varchar;
   res resultset;
begin
   -- build the dynamic SQL query
   query := 'select ...'
   res := (execute immediate :query);
   return table (res);
end;

Upvotes: 1

Gokhan Atil
Gokhan Atil

Reputation: 10039

You can use Tabular SQL UDFs to return the result:

https://docs.snowflake.com/en/developer-guide/udf/sql/udf-sql-tabular-functions.html#calling-a-sql-udtf

Upvotes: 0

Related Questions