Search code examples
sqlsql-servert-sqlsql-server-2008dml

Cannot reseed the Table in sql server

I have below tables that I am trying to delete its record and reseeding it after deleting it and it is throwing an error that I cannot reseed the table, saying that the parameter 3 is incorrect. as can be seen the parameter 3 is going correct as it is a variable

Delete Table1
where Category = 'category'

Declare @NEXTIdentity int = 0
SELECT @NEXTIdentity = MAX(ID) + 1
FROM Table1

    
DBCC CHECKIDENT(Table1, Reseed, @NEXTIdentity)

Error message

Msg 2560, Level 16, State 9, Line 24
Parameter 3 is incorrect for this DBCC statement.

I Tried explicitly setting the variable which works

DBCC CHECKIDENT(Table1, Reseed, 1)
Solution

  • Yup you can pass variables to DBCC (at least with my conf : mssql 16)

    try :

    Delete Table1
where Category = 'category'

Declare @NEXTIdentity int = 0
SELECT @NEXTIdentity = ISNULL(MAX(ID),0) + 1
FROM Table1

    
DBCC CHECKIDENT(Table1, Reseed, @NEXTIdentity)

    Note that NULL + 1 = NULL