creating dynamic table name

• DuruG

  SSCrazy

  Points: 2089

  More actions

  November 13, 2002 at 6:49 am

  #80267

  Hi everybody!

  I'm trying to create a dynamic name for a table like this:

  declare @s1 as char(6)

  set @s1 = '11_2002' --@s1 will be calculated. to make it easier here I hardcoded the value to '11_2002'

  CREATE TABLE [@s1] (

  [Id] int NOT NULL,

  [TID] varchar (15) NULL,

  [TestName] varchar (35) NULL,

  [Tester] int NULL,

  [NumericResult] float NULL,

  [StringResult] varchar (20) NULL,

  [Status] varchar (10) NULL,

  [testDate] datetime NULL

  )

  So, the desired table name will be 11_2002. But he is creating a table named @s1. If I eliminate the brackets around @s1, I receive an error, next to @s1.

  Any ideas?

  Thanks,

  Durug

• paulhumphris

  Hall of Fame

  Points: 3496

  More actions

  November 13, 2002 at 6:58 am

  #441228

  I think you should be able to do this with dynamic sql.

  declare @s1 as char(6)

  declare @sql as varchar(8000)

  set @s1 = '11_2002'

  set @sql = 'CREATE TABLE ' + @s1 +

  ' (id int NOT NULL,

  TID varchar(15) NULL,

  TestName varchar(35) NULL,

  Tester int NULL,

  NumericResult float NULL,

  StringResult varchar(20) NULL,

  Status varchar(10) NULL,

  TestDate datetime NULL)'

  execute (@sql)

  You will need to ensure that the user under which this script runs has CREATE TABLE rights.

• Antares686

  SSC Guru

  Points: 125444

  More actions

  November 13, 2002 at 7:01 am

  #441229

  You cannot dynamically subtitute the name in the fasion your are trying. However you could build a string from the create table statment and concatinate the variables value in. Then using either EXECUTE or sp_executesql (preferred) run the string to get the desired result.