Create a table using a variable

How can I create a table within a stored procedure using a variable passed in.. I keep getting an error..??

I'm using the follwing :

CREATE PROCEDURE usr_Create_ERS_HH_USAGE
@TableName nchar(50)
AS

if not exists (select * from sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)

begin
 set @TableName = '[dbo].' + @TableName
 print @TableName
 
 
    print 'Table does not exists'
    CREATE TABLE @TableName
(
       <Table Internals>
)
end

I get an error on the  create command
simloxAsked:
Who is Participating?
 
simonsabinConnect With a Mentor Commented:
Use this

CREATE PROCEDURE usr_Create_ERS_HH_USAGE
@TableName nchar(50)
AS

if not exists (select * from sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)

begin
  declare @SQL varchar(8000)

 set @SQL = ' CREATE TABLE ' + @TableName + '( ..... )'

 print @TableName
 
 
    print 'Table does not exists'
EXEC (@SQL)

You will need to put the column definititions where the ... are.
0
 
DredwickCommented:
doh! so simple too, you helped me out as well!
Thanks,

Justin
0
All Courses

From novice to tech pro — start learning today.