I have a stored procedure that contains an insert statement using "NEXT VALUE FOR" to use a sequence. The stored procedure works fine in SSMS, but fails to execute when executed from a DB Thing. My DB Service is a SQL Query and uses "EXECUTE". If I remove "NEXT VALUE FOR" from my stored procedure and just hard code a value it all works.
Is there some trick to get my stored procedure to be able to use the "NEXT VALUE FOR"?
Re: post
Hello, thank you for the question. Our JDBC statements are prepared and sent to the DB using a syntax that prevents 'next value for' from being utilized due to restrictions on where this function can appear -- we unfortunately cannot adjust or bypass this mechanism.
One possible workaround is to SELECT the current sequence value (and increment appropriately):
SELECT current_value FROM sys.sequences WHERE name = '<sequence name>';
There are other approaches as well such as writing the value in a table to be read later, or relying on auto sequence increments to determine the next row in the sequence.
Please let us know if the information above gives you a path forward.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
