stored-proceduresparameterssnowflake-cloud-data-platform

Snowflake SQL stored procedure: reference parameter in SQL query


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?


Solution

  • 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);