Identity Seed Error

[dajonx]

Hi,

I'm trying to place a variable as a identity seed. Is this possible? I can't seem to find an answer anywhere... Below is my code:

declare @max1 int

select @max1 = max(acid)+1 from cms_2a
alter table cms_3a add cwid int identity(@max1, 1)

It says that I have an error on "@max1". If I can't place a variable as a seed, how else can I resolve this issue?

Thank you in advance!

 

[mrdenny]

No you can't use a variable within the SEED declaration.

Make the ALTER TABLE command dynamic SQL.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[dajonx]

Thank you!

I'm not familar with dynamic SQL... Could you please give me an example? I've googled it but I wasn't able to find a good example.

 

[mrdenny]

Something like this.

declare @sql VARCHAR(1000)
declare @max1 int

select @max1 = max(acid)+1 from cms_2a

SET @sql = 'alter table cms_3a add cwid int identity(' + cast(@max1 as varchar(10)) + ', 1)'
exec (@sql)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[dajonx]

Thank you very much Denny!! I truly appreciate it!!

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog