1

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'.
*/
Dale K
  • 25,246
  • 15
  • 42
  • 71
Basil Bear
  • 433
  • 3
  • 15
  • 1
    Aside: Database object names, e.g. `@sequence`, ought to be stored in [`sysname`](https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16#remarks) variables and columns. "`sysname` is a system-supplied user-defined data type that is functionally equivalent to `nvarchar(128)`, except that it isn't nullable". The other string lengths should be adjusted accordingly, e.g. `@sql` isn't very roomy. – HABO Jan 04 '23 at 23:47
  • https://stackoverflow.com/users/1127428/dale-k Thanks for the edit – Basil Bear Jan 05 '23 at 11:20

1 Answers1

3

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;
George Menoutis
  • 6,894
  • 3
  • 19
  • 43