snowflake bind variable not set . while i am passing it through proc argument

Question

Here is my code-

CREATE OR REPLACE PROCEDURE some_proc_name("LOAD_MONTH" INTEGER, "LOAD_YEAR" INTEGER)
RETURNS VARCHAR(16777216)
LANGUAGE SQL
EXECUTE AS CALLER
AS declare
    result string;
    v_EXTRACT_DATE DATE;
    cur CURSOR FOR 
        SELECT distinct EXTRACT_DATE
        FROM some_table_name
        where month(EXTRACT_DATE) = :LOAD_MONTH and year(EXTRACT_DATE) = :LOAD_YEAR; 
begin
   return 'SUCCESS';
end;

This stored procedure gets create successfully.
But when i call this with below code, it throws me error.
Code -

 call some_proc_name(1,2025);

Error-

I have also tried multiple other ways like-
- replace :LOAD_MONTH with identifier(:LOAD_MONTH)
- removed "" from LOAD_MONTH proc argument
- put cur variable inside begin block with let cur <all_details>
But, all are throwing same error as before.

Answer 1

The issue lies in how Snowflake handles bind variables (: syntax) inside a SQL stored procedure. Snowflake doesn't allow CURSOR FOR declaration in a static SQL string inside a SQL-language stored procedure.

CREATE OR REPLACE PROCEDURE some_proc_name("LOAD_MONTH" INTEGER, "LOAD_YEAR" INTEGER)
RETURNS VARCHAR(16777216)
LANGUAGE SQL
EXECUTE AS CALLER
AS DECLARE
    v_sql STRING;
    v_EXTRACT_DATE DATE;
    rs RESULTSET;
BEGIN
    -- Step 1: Construct SQL string
    v_sql := '
        SELECT DISTINCT EXTRACT_DATE
        FROM some_table_name
        WHERE MONTH(EXTRACT_DATE) = ' || LOAD_MONTH || '
        AND YEAR(EXTRACT_DATE) = ' || LOAD_YEAR || ';
    ';
    
    -- Step 2: Execute dynamic SQL and store result
    rs := (EXECUTE IMMEDIATE v_sql);

    -- Step 3: Iterate using FOR loop over resultset
    FOR record IN rs DO
        v_EXTRACT_DATE := record.EXTRACT_DATE;

Answer 2

Cursor declaration can contain parameters ?:

BEGIN 
  LET c1 CURSOR FOR 
         SELECT id 
         FROM invoices 
         WHERE price > ? AND price < ?;

OPEN c1 USING (minimum_price, maximum_price);
...
END;

---

Full code:

CREATE TABLE some_table_name(extract_date DATE);
INSERT INTO some_table_name VALUES ('2022-01-01');

CREATE OR REPLACE PROCEDURE some_proc_name(LOAD_MONTH INTEGER, LOAD_YEAR INTEGER)
RETURNS VARCHAR(16777216)
LANGUAGE SQL
EXECUTE AS CALLER
AS 
DECLARE
  result string;
  v_EXTRACT_DATE DATE;
  cur CURSOR FOR
      SELECT DISTINCT EXTRACT_DATE
      FROM some_table_name
      WHERE month(EXTRACT_DATE) = ? and year(EXTRACT_DATE) = ?;
BEGIN

  OPEN cur USING (LOAD_MONTH, LOAD_YEAR);

  -- if only single value
  FETCH cur INTO result;

  --FOR record IN cur DO
  --  result := record.extract_date;
  --END FOR;

  CLOSE cur;

  RETURN result;
END;

Procedure call:

CALL some_proc_name(1, 2022);
-- 2022-01-01

Output: