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