Snowflake Scripting - SQL Cursors

Snowflake scripting in SQL seems to be in preview and I have decided to have a play with it. Given how new it is there is limited documentation so I am using a combination of what I can find on the Snowflake site and the odd blog that has been written about it. There appear to be a few quirks, at least when compared to Oracle PL/SQL (though that has been round for years). How many of these are intentional and how many are things to be ironed out I don't know. You can see the procedure I have created it: 

• Accepts an id as a parameter 
• Creates a result set selecting from a table, using the parameter as a filter
• Loads the results set into a cursor. 
• Loops through the cursor loading the id in the cursor into variable
• Calls procedure passing in the variable as the parameter. 

Then as a proof of concept I tried the Snowflake feature of allowing declaration of variables within the main start and end block. 

	-- standard create or replace procedure
	create or replace procedure p_run_source(p_source_id number) returns number
	language sql as
	-- declare to add variables
	declare
	-- make results for query, allows parameter to be passed in, putting SQL directly in a cursor this did not work
	res RESULTSET := (select csl_id as id
	from KEBOOLA_7127.WORKSPACE_15661914.ICE_CONTROL_SOURCE_LINK
	where source_id = :p_source_id
	);
	-- make a cursor from the results set
	c1 CURSOR FOR res;
	-- a variable, required for later
	numtest number default 0;
	begin
	-- Start the for loop, running for records in cursor c1
	FOR record IN c1 DO
	-- set variable = id from the cursor
	numtest := record.id;
	-- call other procedure using the set parameter, passing id straight in failed
	call p_run_control(:numtest);
	-- end the for loop
	END FOR;
	end ;
	-- in snowflake scripting you can also skip the declare and use the LET syntax to declare within the begin and end block
	create or replace procedure p_run_source(p_source_id number) returns number
	language sql as
	begin
	LET res RESULTSET := (select csl_id as id
	from KEBOOLA_7127.WORKSPACE_15661914.ICE_CONTROL_SOURCE_LINK
	where source_id = :p_source_id
	);
	LET c1 CURSOR for res;
	LET numtest number default 0;
	FOR record in c1 DO
	numtest := record.id;
	call p_run_control(:numtest);
	END FOR;
	return numtest;
	end ;

view raw snowflakeCursor.sql hosted with ❤ by GitHub