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;
Answer by KenJ ·
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;