Reset sequence to a specific value for test purposes

I’d like to reset sequence to specific value for test purposes. First decrement sequence with 100. After test execution I’ll increment sequence with 100.

ALTER SEQUENCE seq_a RESTART WITH (SELECT MAX(id) FROM House.Report) - 100;

Error:

Incorrect syntax nead '(' -

Answer

I would suggest using dynamic SQL:

DECLARE @max INT = (SELECT MAX(id) FROM House.Report) - 100
DECLARE @stmt NVARCHAR(200) = 'ALTER SEQUENCE seq_a RESTART WITH ' + CAST(@max AS CHAR(5))
EXEC(@stmt)