Create a table with a variable table name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Create a table with a variable table name

Author

Topic

jdoering
Starting Member

32 Posts

Posted - 2002-06-18 : 10:15:50

Hi All!

I am working in SQL 2000 and am trying to create a stored procedure that creates a table name based on the variable passed into the stored procedure.

Here is my SQL Statement:

CREATE PROCEDURE CreateProperTables
@sSupplierName as varchar(25)
AS

DECLARE @TableName as varchar(35)
SET @TableName=@sSupplierName+'Product'

CREATE Table @TableName (
[Sup_Mat_Num] [varchar] (50) NULL ,
[Prod_Desc] [varchar] (255) NULL ,
[Prod_Hierarchy] [varchar] (25) NULL ,
[Min_Ord_Qty] [varchar] (25) NULL ,
[Lead_Time] [varchar] (25) NULL ,
[Sample_Lead_Time] [varchar] (25) NULL ,
[Proof_Lead_Time] [varchar] (25) NULL ,
[Prod_Weight] [varchar] (25) NULL ,
[Prod_Unit_Cost] [varchar] (25) NULL ,
[Prod_Soft_Good] [varchar] (25) NULL ,
[Prod_Long_Desc] [varchar] (800) NULL
)
GO

The error that I am receiving is:
Server: Msg 170, Level 15, State 1, Procedure CreateProperTables,
Line 12: Incorrect syntax near '@TableName'.

Any suggestions would be greatly appreciated! Thanks, Julie

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-06-18 : 10:21:26

Hi Julie!

You'll have to use dynamic SQL:

CREATE PROCEDURE CreateProperTables
@sSupplierName as varchar(25)
AS

DECLARE @TableName as varchar(35)
SET @TableName=@sSupplierName+'Product'

DECLARE @sql varchar(4000)
select @sql =
'CREATE Table ' + @tablename + ' (
[Sup_Mat_Num] [varchar] (50) NULL ,
[Prod_Desc] [varchar] (255) NULL ,
[Prod_Hierarchy] [varchar] (25) NULL ,
[Min_Ord_Qty] [varchar] (25) NULL ,
[Lead_Time] [varchar] (25) NULL ,
[Sample_Lead_Time] [varchar] (25) NULL ,
[Proof_Lead_Time] [varchar] (25) NULL ,
[Prod_Weight] [varchar] (25) NULL ,
[Prod_Unit_Cost] [varchar] (25) NULL ,
[Prod_Soft_Good] [varchar] (25) NULL ,
[Prod_Long_Desc] [varchar] (800) NULL
)'

exec (@sql)
GO

Regards,
Kalle Dahlberg

Edited by - andraax on 06/18/2002 10:22:05

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-18 : 10:21:26

Dynamic SQL:

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

jdoering
Starting Member

32 Posts

Posted - 2002-06-18 : 10:32:28

Thank you so much!!! It works now. -Julie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources