Snowflake SQL stored procedure: reference parameter in SQL query

Question

I have been trying to figure out a smooth way to use parameters in a Snowflake SQL stored procedure using this syntax:

CREATE OR REPLACE PROCEDURE Schema1.TestProc (Param1 FLOAT)
  RETURNS VARCHAR
  LANGUAGE SQL
  AS
  DECLARE CursorInst CURSOR FOR SELECT MIN(XID) as MinXID, MAX(XID) as MaxXID
                            FROM (SELECT XID,NTILE(10) OVER (ORDER BY XID) as NumBatch
                                    FROM Schema1.Table1
                                    WHERE Active = 1
                                    AND XID IN (SELECT DISTINCT Table1XID FROM Schema1.Table2
                                                WHERE DateCreated::DATE >= CURRENT_TIMESTAMP::TIMESTAMP::DATE - :Param1)) t
                                    GROUP BY NumBatch;
    Processed INTEGER DEFAULT 0;
    
  BEGIN
     FOR Record IN CursorInst
      DO
        Processed := Processed + 1;
     END FOR;

  RETURN 'Success';
  END

but I get the error:

Bind variable :Param1 not set. (line 301)

Any way to do this using similar syntax?

Lukasz Szozda · Accepted Answer · 2025-05-15 17:25:16Z

1

Yes, it is possible to parametrize the query used for cursor: Cur CURSOR FOR SELECT... FROM ... WHERE col > ? and then OPEN Cur USING (:Param):

CREATE OR REPLACE PROCEDURE Schema1.TestProc (Param1 FLOAT)
  RETURNS VARCHAR
  LANGUAGE SQL
  AS
  DECLARE 
     CursorInst CURSOR FOR 
            SELECT MIN(XID) as MinXID, MAX(XID) as MaxXID
            FROM (SELECT XID,NTILE(10) OVER (ORDER BY XID) as NumBatch
                  FROM Schema1.Table1
                  WHERE Active = 1
                    AND XID IN (SELECT DISTINCT Table1XID 
                                FROM Schema1.Table2
                                WHERE DateCreated::DATE >= 
                                      (CURRENT_TIMESTAMP()::DATE - ?::INT))) t
            GROUP BY NumBatch;

    Processed INTEGER DEFAULT 0;
    
  BEGIN
     OPEN CursorInst USING (:Param1);
     FOR Record IN CursorInst
      DO
        Processed := Processed + 1;
     END FOR;

     CLOSE CursorInst;

  RETURN 'Success';
  END;


CALL Schema1.TestProc(1);

EDIT:

just reference it directly with :Param syntax

Technically it is possible but first you define RESULTSET and then cursor:

CREATE OR REPLACE PROCEDURE Schema1.TestProc (Param1 FLOAT)
  RETURNS VARCHAR
  LANGUAGE SQL
  AS
  DECLARE 
     RES RESULTSET := (
        SELECT MIN(XID) as MinXID, MAX(XID) as MaxXID
        FROM (SELECT XID,NTILE(10) OVER (ORDER BY XID) as NumBatch
              FROM Schema1.Table1
              WHERE Active = 1
                AND XID IN (SELECT DISTINCT Table1XID 
                            FROM Schema1.Table2
                            WHERE DateCreated::DATE >= 
                                  (CURRENT_DATE() - :Param1::INT))) t
        GROUP BY NumBatch);

    Processed INTEGER DEFAULT 0;
    
  BEGIN
     LET CursorInst CURSOR FOR res;
     FOR Record IN CursorInst
      DO
        Processed := Processed + 1;
     END FOR;

     CLOSE CursorInst;

  RETURN 'Success';
  END;


CALL Schema1.TestProc(1);

edited May 15 at 17:25

answered May 15 at 16:35

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EliSquared May 15 at 17:15

Perfect. Though I dont understand why they cant just Pre-declare any of the stored procedure parameters when using SQL as the language so that you can just reference it directly with :Param syntax.

Lukasz Szozda May 15 at 17:25

@EliSquared That is also possible. Though first you define RESULTSET and then loop over it with a cursor

Collectives™ on Stack Overflow

Snowflake SQL stored procedure: reference parameter in SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related