Use of variable in result_scan() snowflake stored procedure-CodePudding

I am trying to store the result of a last_query_id() as a variable and then later on in the stored procedure I am trying to use it in a table(result_scan()). I am coming across a number of errors when doing this, however I'm sure it's my level of knowledge of how snowflake works that s the problem. My code currently looks like this:

Declare query_id as varchar;
...
a number of queries;
...

query_id := (select last_query_id());
...
Some more queries
...

let c1 CURSOR for select * from table(result_scan(:query_id));

This is giving me an error saying result_scan() requires a string. I have tried using CAST to convert it to a string however this is not working either.

Thanks!

CodePudding user response：

the syntax for assigning the result of a SELECT to a variable is

SELECT COL1 ... INTO variable

It's documented here

CodePudding user response：

The assignment is possible:

BEGIN
  LET query_id varchar;

  SELECT 1 AS c;

  query_id := (select last_query_id());

  RETURN :query_id;

END;