How to Write Parameterized Queries in Snowflake?

Snowflake supports almost all features that are available in legacy relational databases such as Oracle, Teradata, etc. Along with those features, Snowflake provides many extensions that will allow you to write efficient queries. One of such features is ability to SET and pass variables to the query, In my other articles, we have discussed how to create parameterized views in Snowflake. In this article, we will check how to write parameterized queries in Snowflake with the help of the SET command.

Why Do We Need Queries with Parameters in Snowflake?

Before going into method to write parameterized queries in Snowflake, firstly, let us understand why do we need Parameterized queries?

Sometimes, you may get requirement to change only the condition in the WHERE clause in your query to pull specific records. Instead of creating a separate query for each condition, you can create single query and pass the condition value as a parameter. This also reduces maintenance overhead.

How to Write Parameterized Queries in Snowflake?

You can use the session variables in conjunction with a your query. You can refer a session variable in the query. Set session variable before executing the query.

For example,

  SELECT source_id,
         source_name
  FROM   rec_test
  WHERE  source_id = $where_c; -- Session variable

How to use session variable?

You can use the SET command to set session variable. A SET command initializes the value of a session variable to the result of a SQL expression or a constant value.

Following example demonstrate the use of session variable inside query.

-- Session variable for WHERE condition
SET WHERE_C = '10';

-- Query with session variable
  SELECT source_id,
         source_name
  FROM   rec_test
  WHERE  source_id = $where_c; -- Session Variable

Following is the output with different WHERE conditions.

-- Result when SOURCE_ID is set to 10
SET WHERE_C = '10';
SELECT source_id,
         source_name
  FROM   rec_test
  WHERE  source_id = $where_c;
+-----------+-------------+
| SOURCE_ID | SOURCE_NAME |
|-----------+-------------|
|        10 | Chris Speed |
+-----------+-------------+

-- Result when SOURCE_ID is set to 1
SET WHERE_C = '1';
SELECT source_id,
         source_name
  FROM   rec_test
  WHERE  source_id = $where_c;
+-----------+-----------------+
| SOURCE_ID | SOURCE_NAME     |
|-----------+-----------------|
|         1 | Robert C. Allen |
|         1 | Robert C. Allen |
+-----------+-----------------+

Snowflake Identifier Function

You have to use to identifier function when referring any session variable in SELECT or FROM clause. For example, when you are passing table name using session variable.

For example,

set temp_v = 't1';

SELECT col1,
       col2
FROM   identifier($temp_v); -- session variable

Without identifier function, you may end up with a compilation error.

For example,

001003 (42000): SQL compilation error:
syntax error line 3 at position 7 unexpected '$temp_v'.