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?

The documentation is quite clear - the syntax diagram is full of <constant> and nothing else for specifying the values. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 1, 2014 at 7:34
Thanks; deans answer solves the problem given the restrictions. — joshhendo
– joshhendo, Commented May 1, 2014 at 10:55

dean · Accepted Answer · 2014-05-01 07:21:54Z

27

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);

answered May 1, 2014 at 7:21

dean

10.1k2 gold badges27 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vishnuudayan · Accepted Answer · 2022-03-10 13:59:44Z

-1

Try this:

select setval('seq_name', variable, true);

answered Mar 10, 2022 at 13:59

vishnuudayan

191 silver badge3 bronze badges

1 Comment

Muhammad Hewedy Over a year ago

[S0010][195] Line 4: 'setval' is not a recognized built-in function name.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related