snowflake-cloud-data-platformsequence

Error when running NEXTVAL on sequence inside stored procedure


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?


Solution

  • 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:

    enter image description here