Using Snowflake scripting I need to bind the input parameter of the stored procedure which is an array and it will be part of array_contains function in dynamic sql however its throwing bind error
1 Answers
It is possible to use ARRAY data type as a bind argument inside Snowflake Scripting:
Sample data:
CREATE OR REPLACE TABLE t(i INT, j TEXT);
INSERT INTO t(i, j) VALUES (1,'a'), (2, 'b'), (3, 'c'), (4, 'd');
Stored procedure:
CREATE OR REPLACE PROCEDURE sp_array_arg(ARG ARRAY)
RETURNS TABLE(i INT, j TEXT)
LANGUAGE SQL
AS
$$
DECLARE
res RESULTSET default (SELECT i, j
FROM t
WHERE ARRAY_CONTAINS(t.i::VARIANT, :ARG));
BEGIN
RETURN TABLE(res);
END;
$$;
Procedure call:
CALL sp_array_arg([1,3]);
/*
I J
1 a
3 c
*/
Related