I want to create a table from a stored procedure where the name is passed to it:

I have this:
************************************************** ********

CREATE PROCEDURE sp_TP_CreateTable

(
@TABLENAME as varchar
)

AS

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@TABLENAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@TABLENAME]

CREATE TABLE [dbo].[@TABLENAME] (
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Catalog Version] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mailed] [float] NULL ,
[Sales] [money] NULL ,
[Production $] [int] NOT NULL ,
[Orders] [float] NULL ,
[Response] [float] NULL ,
[Response of Test Control] [int] NOT NULL ,
[Average Invoice] [float] NULL ,
[SMP] [float] NULL ,
[SMP of Test Control] [int] NOT NULL ,
[Catalog Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Brand] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Drop Date] [smalldatetime] NULL
) ON [PRIMARY]
GO


But it's createing a table called @TABLENAME How can I get this to work?

Thanks,

Ken

This is just a guess as I don't know SQL Server at all well, but I imagine all those horrible [square brackets] are there to show that values are literals, so maybe the correct syntax would be:

CREATE TABLE [dbo].@TABLENAME (

...

Or maybe I'm totally wrong!

Tried that... dbo.@TABLENAME

I get and error incorrect syntax near @TABLENAME

Why dbo.@TABLENAME and not [dbo].@TABLENAME ?

I tried it both ways and got the same error message.

There has got to be a way to do this. I suppose I can use vb to do what I want, but the stored procedure seemed the logical way to do it in the first place.

Found this via Google:

A common question asked of SQL Team is "How do I write a stored procedure that will create a table/database. I want to pass in the name"

SQL Server will not allow this

Create Table @TableName (
ID int NOT NULL Primary Key,
FieldName VarChar(10)
)

Once again, dynamic SQL to the rescue

Declare @SQL VarChar(1000)

SELECT @SQL = 'Create Table ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'

Exec (@SQL)

URL: http://www.sqlteam.com/item.asp?ItemID=4619

I tried this, and it worked: