Passing Query Results Within a Stored Procedure in Snowflake

How-Tos FAQs
September 7, 2020

Get Started Transforming Your Data in Snowflake

Passing Query Results Within a Stored Procedure in Snowflake

We worked on a rather simple use-case, converting our traditional database SQL stored procedures into Snowflake stored procedures. This simple task can become so difficult very quickly, especially if the JavaScript knowledge is minimal.

In our specific use case, we are passing the results of one query as input to another query within the same procedure. Unfortunately, we searched a bit and couldn’t find anything relevant that would help achieve this within the same procedure.

Passing query results within a Stored Procedure

Nevertheless, after a lot of trial and error, we were able to achieve said goal and thought to share it with you.

First things first, let’s create some sample data for our work, from here (add link):

CREATE OR REPLACE DATABASE TEST_1;
USE DATABASE TEST_1;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE ORDERS
(
id NUMBER,
date DATE,
customer_name STRING,
city STRING,
amount NUMBER(10, 2)
);

INSERT INTO orders
SELECT 1,'08/01/2021','Mike Engeseth','Austin',10000
UNION ALL  
SELECT 2,'08/02/2021','Mike Jones','Dallas',20000
UNION ALL  
SELECT 3,'08/03/2021','John Engeseth','Boston',5000
UNION ALL  
SELECT 4,'08/04/2021','Michael Engeseth','Austin',15000
UNION ALL  
SELECT 5,'08/05/2021','Mike Coulthard','Boston',7000
UNION ALL  
SELECT 6,'08/06/2021','Paul Engeseth','Austin',25000
UNION ALL
SELECT 7,'08/10/2021','Andrew Engeseth','Dallas',15000
UNION ALL  
SELECT 8,'08/11/2021','Mike Shelby','Dallas',2000
UNION ALL  
SELECT 9,'08/20/2021','Robert Engeseth','Boston',1000
UNION ALL  
SELECT 10,'08/25/2021','Peter Engeseth','Austin',500
;

Passing Query Results Within a Stored Procedure

This is how your final data set should look like.

CREATE OR REPLACE PROCEDURE PassingQueryResultsSP()
RETURNS string
LANGUAGE javascript
EXECUTE AS owner
AS
$$

Now, let’s create a simple stored procedure for our purpose.

// Min Value
var min_value = 0;
var min_command = `SELECT MIN(amount) FROM TEST_1.PUBLIC.orders`;

// Run the statement
var min_stmt = snowflake.createStatement({sqlText: min_command});
var res = min_stmt.execute();

// Get back the value
res.next();

// Get the first returned column value within
minimum_value = res.getColumnValue(1);

We’re essentially querying for the minimum ‘Amount’ value in our ‘ORDERS’ table and storing the result in a variable called ‘minimum_value’.

// Max Value
var max_value = 0;
var max_command = `SELECT MAX(amount) FROM TEST_1.PUBLIC.orders`;

// Run the statement
var max_stmt = snowflake.createStatement({sqlText: max_command});
var res = max_stmt.execute();

// Get back the value
res.next();

// Get the first returned column value within
maximum_value = res.getColumnValue(1);

Similarly, we’re getting the maximum ‘Amount’ value and storing it in ‘maximum_value’.

// Using the above two variables in a sql query
var sql_value = '';
var sql_command = `SELECT customer_name FROM TEST_1.PUBLIC.orders WHERE amount BETWEEN ` + minimum_value + ` AND ` + maximum_value + ` LIMIT 1`;

// Run the statement
var sql_stmt = snowflake.createStatement({sqlText: sql_command});
var res = sql_stmt.execute();

// Get back the value
res.next();

// Get the first returned column value within
sql_value = res.getColumnValue(1);

return sql_value;

$$
;

Finally, we are running a query to get the first name that pops up when we search for customer names with amounts between the minimum_value and maximum_value. And, we are going to return this value.

CALL PassingQueryResultsSP();

Passing Query Results Within a Stored Procedure

Let’s call this stored procedure and check the results. As expected, we got ‘Mike Engeseth’.

We have successfully created two queries for min and max amount stored the results in two variables. Then, we used those two variables to filter another query and return a final answer, all from a single procedure call. And that is how passing Query results within a stored procedure in Snowflake works.