SQL Server: pass next value of sequence to procedure

Question

I'm new to Microsoft SQL Server. I have to call an existing procedure and I want to pass the next value of an sequence.

Here a rough overview of my objects and the way I want to pass the values:

I've created an sequence, like

CREATE SEQUENCE mySeq START WITH 10000 CACHE 50;

Also, I created a procedure like

CREATE PROCEDURE P_MY_PROCEDURE
     (@V_TECHNICAL_ID BIGINT, @V_VALUE VARCHAR(254))
AS
BEGIN
    INSERT INTO mytable (key, value) 
    VALUES (@v_technical_id, @v_value);
END@

Now, I would like to call the procedure mentioned above the following way:

EXEC P_MY_PROCEDURE NEXT VALUE FOR mySeq, 'Some Value';

While using the previous line, I get the following error:

[Error Code: 102, SQL State: S0001] Incorrect syntax near 'NEXT

What is wrong? With other RDBMS (like Oracle, DB2), the way execution the procedure works without any problems

Xedni · Accepted Answer · 2017-09-27 21:05:28Z

3

Stored procedures can only be called with literal values or variables/parameters. No functions (user defined or otherwise), or other SQL constructs. So you have to assign next value for mySeq to something first. Another example of this same phenomenon is if you were to try to pass getdate() into a procedure; it won't let you.

Try this:

declare @seqval int = next value for myseq

exec P_MY_PROCEDURE @seqval, 'some value'

answered Sep 27, 2017 at 21:05

Xedni

4,7913 gold badges22 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server: pass next value of sequence to procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related