Use a variable or parameter to specify restart value for ALTER SEQUENCE

Question

I have a situation where I need to restart a sequence to a specified value, where this value is specified in either a variable or passed as a parameter programically from a C# program.

The following code is an example of what I hoped would work, but didn't:

DECLARE @current_value AS BigInt = 60000;

ALTER SEQUENCE
    usq_MySequence
RESTART WITH
    @current_value

Something like this does work:

ALTER SEQUENCE
    usq_MySequence
RESTART WITH
    60000

however that's hard coded, and the program I'm interacting with will only pass parameters (Using the SqlCommand in .NET)

Is there anyway to reset a sequence with a variable or parameter?

Answer 1

The RESTART WITH value must be a constant. The only way to set it with a variable is to use some dynamic SQL:

DECLARE @current_value AS BigInt = 60000;
DECLARE @s nvarchar(1000);

SET @s = N'
ALTER SEQUENCE
    usq_MySequence
RESTART WITH ' + CAST(@current_value AS nvarchar(10));

EXEC (@s);