Help with Reseed of a table

• TSQL Tryer

  SSCrazy Eights

  Points: 8869

  More actions

  March 7, 2016 at 2:49 am

  #309184

  Hi there,

  Getting the following error which I think I understand -

  Checking identity information: current identity value '0'.

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Msg 2560, Level 16, State 9, Line 6

  Parameter 3 is incorrect for this DBCC statement.

  What I am trying to achieve is when deleting a table I want to either reseed it to "1" if there is nothing left in the table - Or reseed it to the last id that is still in the table. Below is what I have so far which is giving me the error - which I presume is because there in now nothing in that table, but I need a way for it to work either or scenarios -

  The delete is here -

  delete from cgl where LTId=2 --Where CGL is an Agreement Episode

  So "potentially" some data that is not LTid will be left behind. But as in this case the table is actually empty I get the above error.

  if (select count(0) from CGL) = 0

  dbcc checkident ('CGL', RESEED, 0)

  else

  declare @lastc int

  select @lastc=max(CCGLId) from CGL

  DBCC CHECKIDENT ('CGL', RESEED, @lastc)

  What would you recommend?

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  March 7, 2016 at 4:14 am

  #1863421

  You are executing the last 2 statements regardless of how many rows exist in the table. I assume this is the logic you really meant...

  if (select count(0) from CGL) = 0

  dbcc checkident ('CGL', RESEED, 0)

  else

  BEGIN

  declare @lastc int

  select @lastc=max(CCGLId) from CGL

  DBCC CHECKIDENT ('CGL', RESEED, @lastc)

  END

• Eirikur Eiriksson

  SSC Guru

  Points: 182915

  More actions

  March 7, 2016 at 4:21 am

  #1863422

  BEGIN and END are your friends:-D

  😎

  if (select count(0) from CGL) = 0

  BEGIN

  dbcc checkident ('CGL', RESEED, 0)

  END

  else

  BEGIN

  declare @lastc int

  select @lastc=max(CCGLId) from CGL

  DBCC CHECKIDENT ('CGL', RESEED, @lastc)

  END

• TSQL Tryer

  SSCrazy Eights

  Points: 8869

  More actions

  March 7, 2016 at 5:16 am

  #1863431

  Thanks Guys.

• s.wileman

  Grasshopper

  Points: 11

  More actions

  July 22, 2019 at 3:10 pm

  #3665110

  This thread helped me but the above could be reduced to:

  DECLARE @lastc int;
  
  SELECT @lastc = ISNULL(MAX(CCGLId), 0) FROM CGL;
  
  DBCC CHECKIDENT ('CGL', RESEED, @lastc);

  • This reply was modified 5 years, 5 months ago by  s.wileman. Reason: Code formatting