CODEWITHSUNDEEP
snowflake-cloud-data-platform
Vidya821
Vidya821

Reputation: 77

Stored procedure - Use array list in SQL query for insert in snowflake

If i have created/generated a list of elements during processing in stored procedure say rownum = [1,2,3,4]. Now i want to use this list in a sql statement to filter out rows say select * from mytable where rownum not in (1,2,3,4) in same stored procedure. How can i achieve this ? Please guide. Thanks

Upvotes: 0

Views: 2864

Answers (2)

user13472370
user13472370

Reputation:

The general solution to this would be to use binding variables. However, set types are not supported as bind variables in the Stored Procedure APIs currently.

The JavaScript APIs do permit you to generate your SQL dynamically using string and array transform functions, so the following approaches can be taken to work around the problem.

Inline the list of values into the query by forming a SQL syntax of a set of values:

CREATE OR REPLACE PROCEDURE SAMPLE()
    RETURNS RETURNTYPE
    LANGUAGE JAVASCRIPT
    AS
    $$
      var lst = [2, 3, 4]
      var lstr = lst.join(',') // "2, 3, 4"
      var sql_command = `SELECT * FROM T WHERE C NOT IN (${lstr})` // (2, 3, 4)
      var stmt = snowflake.createStatement( {sqlText: sql_command} )
      // Runs: SELECT * FROM T WHERE C NOT IN (2, 3, 4) [Literal query string]
      [...]
    $$;

Or if the list of values used could be unsafe, you can generate the query to carry just the right number of bind variables:

CREATE OR REPLACE PROCEDURE SAMPLE()
    RETURNS RETURNTYPE
    LANGUAGE JAVASCRIPT
    AS
    $$
      var lst = [2, 3, 4]
      var lst_vars = Array(lst.length).fill("?").join(", ") // "?, ?, ?"
      var sql_command = `SELECT * FROM T WHERE C NOT IN (${lst_vars})` // (?, ?, ?)
      var stmt = snowflake.createStatement( {sqlText: sql_command, binds: lst} )
      // Runs: SELECT * FROM T WHERE C NOT IN (2, 3, 4) [After bind-substitution]
      [...]
    $$;

Upvotes: 1

Mike Gohl
Mike Gohl

Reputation: 737

Snowflake has the ARRAY_CONTAINS( , ) function.

example: Array_Contains( 5, array_construct( 1,2,3,4))

Upvotes: 0

Related Questions