My requirement is to retrieve into a variable, the next value for a sequence, the name of which is derived from a string and a value.
When I run the following as a test using exec sp_executesql ... an error is reported:
Incorrect syntax near @sequenceName
What's wrong with my code?
DECLARE @nextSeqID varchar(10);
DECLARE @sql nvarchar(100);
DECLARE @eqtypeID int;
DECLARE @sequence nvarchar(50);
DECLARE @paramdef nvarchar(100);
SET @eqtypeID = 7;
SET @sequence = 'dbo.seq_eqtype_autoserno_' + CAST(@eqtypeID as nvarchar(8));
-- @sequence = dbo.seq_eqtype_autoserno_7
SET @sql = N'SELECT @nextSeqID_OUT = NEXT VALUE FOR @sequenceName';
-- @sql = SELECT @nextSeqID_OUT = NEXT VALUE FOR @sequenceName
SET @paramdef = N'@nextSeqID_OUT varchar(10) OUTPUT, @sequenceName nvarchar(50)';
-- @paramdef = @nextSeqID_OUT varchar(10) OUTPUT, @sequenceName nvarchar(50)
EXEC sp_executesql @sql, @paramdef, @sequenceName = @sequence, @nextSeqID_OUT = @nextSeqID OUTPUT;
/*
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@sequenceName'.
*/
It is admirable and correct that you are using sp_executesql to pass dynamic things through variables. However, you can not do this with object names(like a sequence) and other stuff which are required by the query at runtime.
Remove @sequenceName from the parameters and the definition, and put it directly on the code. The correct way to do this to still be safe from injection is to use it within quotename, so whatever injection attack happens, it will be quoted, and thus safe:
SET @sql = N'SELECT @nextSeqID_OUT = NEXT VALUE FOR '+quotename(@sequenceName);
SET @paramdef = N'@nextSeqID_OUT varchar(10) OUTPUT';
EXEC sp_executesql @sql, @paramdef, @nextSeqID_OUT = @nextSeqID OUTPUT;