DDL Stored Procedure

• anakin

  SSC Enthusiast

  Points: 192

  More actions

  July 17, 2002 at 2:21 am

  #79067

  Is there a way to pass table's name to a stored procedure?

  This is what I am trying to do:

  CREATE PROCEDURE NAME_PROC

  (

  @TABLENAME varchar(30)

  )

  AS

  CREATE TABLE @TABLENAME (OID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,......)

  GO

  but raise a sintax error near @TABLENAME

• Andy Jones, DBA

  Hall of Fame

  Points: 3641

  More actions

  July 17, 2002 at 3:23 am

  #432853

  You would need to use dynamic sql for this task:-

  CREATE PROCEDURE NAME_PROC

  (

  @TABLENAME sysname

  )

  AS

  declare @sql varchar(1000)

  select @sql = 'CREATE TABLE ' + @TABLENAME + ' (OID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,......)'

  exec (@sql) --Could also use sp_executesql here.

  Regards,

  Andy Jones

  .