Tactically return multiple values from Stored Procedure in Snowflake

Prerequisite:
How to create a stored procedure | Snowflake Stored Procedures | Snowflake
Pass argument to the Snowflake procedure

Code:
create or replace procedure demostoredprocedure()
returns string not null
language javascript
as
$$
var my_sql_command = "select department,sum(salary) from DEMO_DB.PUBLIC.EMPLOYEE group by(department)";
// Loop through the results, processing one row at a time...Note : this part is optional , based on requirements
var summation=''
}
return summation; // Replace with something more useful.
$$
;

call demostoredprocedure()

Check this playlist for more AWS Projects in Big Data domain:

[ratulroy]

This is good. If we want to return data dynamically instead of giving hardcoded value into getColumnValue, I want that value should come through loop. Can you demonstrate that? Suppose I want all the column to be returned and using * in the select query. In future if we add column in the table that will also return without touching the proc.

[mcintoda]

Strings suck - how about returning JSON objects (VARIANT)?

[Ajjeshh]

can we implement the same in SQL language instead of Java?

[reddyram]

I have an question when I tried to execute multiple selects by adding union to all sqls which dynamically genarated it's appending the key word to every SQL statement including first and last as well which cashing issue to me can u guid me
ex: union sel col from table union sel col from table like that my sqls are genarated, so how can I skip union at first/last line

[eleanorrigby]

now what if i want to separate this result like sales-11700 finance-13200 etc and place them in a new table ? like currently this is in the form of a single string right but what should i do if i want to place them separately in different columns in a table?can you please help me with this?

[कलयुग_कर्ण]

For example consider a student table and student_info table two table has same data if any changes happened in student table like insert delete update this same changes can be capture and load in student_info table please send the code

[sunshadow]

It is very poor solution. Now think about it. Good solution must be as universal as possible. In real world, comma or any other separator can be part of sql return field; therefore offered approach fails right there.

[naveenchowdarynaidu]

@knowledge Amplifier. I am getting results something like this, DEPT_UUID,DEPT_ID how can i remove the preceding,

I am expecting to have results like this DEPT_UUID, DEPT_ID

My Results from select quey:
Row
COLUMN_NAME
1
DEPT_UUID
2
DEPT_ID

create or replace procedure demostoredprocedure()
returns varchar
language javascript
as
$$
var my_sql_command = "SELECT COLUMN_NAME FROM information_schema.columns where table_name='DEPT_TGT' and IS_NULLABLE='NO'";
var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
var result_set1 = statement1.execute();
// Loop through the results, processing one row at a time...Note : this part is optional, based on requirements
var summation='';

while (result_set1.next()) {
summation=summation+', '+result_set1.getColumnValue(1);
}
return summation; // Replace with something more useful.

$$
;