Trying to use a parameter creating an identity column.

• fjmorales

  Default port

  Points: 1451

  More actions

  May 18, 2009 at 1:20 pm

  #130395

  Hi:

  I'm trying to do this:

  declare @maxid as int

  set @maxid = (select maxid from MAXIDCUSTOMER) + 1

  alter table att_list_2

  add recid [int] IDENTITY(@maxid,1) NOT NULL

  but it gives me an error:

  "Incorrect syntax near '@maxid'."

  Is it possible to use a parameter on the identity?

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  May 18, 2009 at 1:31 pm

  #996589

  I don't think it's possible to use variables in DDL statements. You have use a dynamic SQL statement.

• fjmorales

  Default port

  Points: 1451

  More actions

  May 18, 2009 at 1:40 pm

  #996600

  I just solved it:

  declare @maxid as int

  set @maxid = (select maxid from MAXIDCUSTOMER) + 1

  declare @query as varchar(200)

  set @query = 'alter table att_list_2 add recid [int] IDENTITY(' + cast(@maxid as varchar(10)) + ',1) NOT NULL'

  exec (@query)

  Thanks for the help anyway!