question

limpet2 avatar image
limpet2 asked

Creating stored procedure to call sequence object as input parameter

I'm trying to create an SP with input and output parameters, the input is the sequence object, however I get syntax error: "Incorrect syntax near '@tcField'.

CREATE PROCEDURE [dbo].[uspGetno]
@tcField char(40),
@lnReturn int OUTPUT
As
SET @lnReturn = NEXT VALUE FOR @tcField;
stored procedures
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered

You aren't actually passing a sequence object in as a parameter, that is just a character string. For example,

select next value for 'some_string'

isn't a valid query when it's a string literal and still doesn't work when the string is contained in a variable. If your calling routine knows which sequence it needs to use, maybe you could have it access the sequence directly rather than using the stored procedure?

If you must use the stored procedure, you're going to have to execute some dynamic sql to get to the sequence represented by the string. Maybe something like...

CREATE PROCEDURE [dbo].[uspGetno]
@tcField char(40),
@lnReturn int OUTPUT
As

declare @query varchar(255);
set @query = 'select next value for ' + @tcField;

create table #seq (val int);

insert into #seq
exec(@query);

select @lnReturn = val from #seq;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.