sqlsnowflake-cloud-data-platform

Use bind variable directly in snowflake SQL worksheet

I wanted to try this SQL to use bind variables as input (not in a stored procedure or anonymous block), but it's throwing an error.

LET VARIABLE VARCHAR := 'RPT';
SELECT * FROM TABLE_NAME WHERE TYPE= :VARCHAR

I see this message:

Syntax error: unexpected 'LET'. (line 26)

How can I resolve this?

Solution

  • Snowflake:

    SET rpt_var = 'RPT';
SELECT * FROM TABLE_NAME WHERE TYPE = $rpt_var;

    Snowflake does not support bind variables directly inside plain SQL statements without using session variables (SET) or scripting. Snowflake currently has no syntax for direct bind variables in standalone SQL queries without SET or scripting.

    You can try below workarounds:

    1. Client-side binding: Use your programming language’s Snowflake driver, which supports parameter binding.
    2. Session variables: Use SET and $var inside your SQL (which you want to avoid).
    3. Hardcoding: You must embed the literal values in the SQL.