Merge parameters or variables of stored procedure into another table in Snowflake

Stored procedures are used to run a script that can contain several SQL queries, which are run in sequential order. Let’s look at how to merge parameters or variables of stored procedure into another table in Snowflake.

How to: Merge parameters or variables of stored procedure into another table in Snowflake

One very popular use case for stored procedures is allowing end-users to input variables, which can be used to create dynamic SQL statements.

Stored procedures can have variables, just like functions. In certain use cases, there may be a requirement to keep track of which variables have been used within a stored procedure, possibly to prevent the same variable from being input more than once.

Another use case might require usage statistics for a stored procedure, like the count of how many times a variable or combination of variables was used as input to a stored procedure.

A common use case for a stored procedure is a dynamic query that selects a subset of a larger data set using a date range. The stored procedure input variables might be a start and end date in this particular use case. If variables of a stored procedure are dates in the ranges of interest, you could collect statistics on the dates of interest to your end-users. These statistics could help optimize the underlying data set by removing historical records that haven’t ever been accessed.

String-Variable Concatenation

One way to access input parameters of a stored procedure in a query within the stored procedure is to terminate the string in the body of your store procedure, concatenate the variable and then concatenate another string with the rest of the SQL query. Here’s a simple example.

create or replace procedure end_string_example(some_table VARCHAR)
returns float 
language javascript
as
$$
var cmd = 'select count(*) from ' + some_table; 
var stmt = snowflake.createStatement(
         {
           sqlText: cmd,
         }
     );
var res = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

Variable Binding

Another way to access input variables within your stored procedure is to use variable binding. This is done within the connection execution or within the statement creation. Here’s the basic syntax.

connection.execute({
sqlText: 'select * from :1 where id = :2;',
binds: ['units', 1234]
});

Here’s a more complete example of using variable binding in Snowflake stored procedures. This example demonstrates a way to implement our original use case by storing the variables used in a stored procedure in another table.

create or replace procedure binding_variables(sales_rep VARCHAR, category VARCHAR)
returns float 
language javascript
as
$$
var cmd = `merge into rep_usage as r 
using (SELECT :1 as sales_rep, :2 as category) as v 
                            on r.representative = s.sales_rep 
                            and r.department = s.category 
                            when matched then update 
                            set r.representative = r.representative 
                            when not matched then insert 
                            (representative, department) VALUES (:1,:2)`;

snowflake.execute({sqlText: cmd, binds: [sales_rep, category]});

var cmd = 'select count(*) from sales where sales_rep = :1 and category = :2;'; 
var stmt = snowflake.createStatement(
         {
           sqlText: cmd,
         }
     );
var res = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

By binding variables within stored procedures, you can do much more than simply insert the variables into another table.

Binding variables can also be used to insert records into a new table in bulk. There is an upper limit on how many records you can insert using binding variables.

So now you know how to merge parameters or variables of stored procedure into another table in Snowflake. by binding variables and storing them within a table, and you can do a lot more with them by binding them to your SQL statement.

Continue reading:

Check out: What are the options when it comes to handling data lineage in Snowflake?