I have a snowflake procedure in which I would like to capture the next value from a sequence and do something with it. Here is a simplified example of such a sequence and proc:
use schema TEST_DB.TEST_SCHEMA;
create or replace sequence TEST_DB.TEST_SCHEMA.test_seq;
create or replace procedure TEST_DB.TEST_SCHEMA.test_seq_err()
returns number
language sql
as
begin
return TEST_DB.TEST_SCHEMA.test_seq.nextval;
end;
call test_seq_err();
That yields an error
error line TEST_DB at position {1} invalid identifier '{2}'
Note, this was after I was originally just referencing the sequence like return test_seq.nextval
in which case it says TEST_SEQ is an invalid identifier
(much like described in this SO post. That post suggested fully qualifying the name, but then I got the strange error above.
I created the sequence so I am it's owner, and I have permissions to use the sequence because if I just run select test_seq.nextval
outside of a procedure, it works just fine.
What am I missing to be able to get the next value from this sequence inside a procedure body?
SELECT
can be used inside Scripting Block too:
create or replace procedure TEST_DB.TEST_SCHEMA.test_seq_err()
returns number
language sql
as
begin
return (SELECT TEST_DB.TEST_SCHEMA.test_seq.nextval);
end;
Output: