SP To create table

[Michael Jackson]

I have a stored procedure to create a table. In my program I want the user
to name the table to be created, therefore I pass a parameter to the SP for
the table name. I cannot get it to work.

It creates a table called "@NewTableName" .

Any ideas?

CREATE PROCEDURE dbo.sp_FFProduc tion_CreateTabl e
(
@NewTableName nvarchar(128)
)
AS
CREATE TABLE [dbo].[@NewTableName]
(
[ProductionID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductionName] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[ProductionDate] [datetime] NOT NULL ,
[DocumentID] [int] NOT NULL,
[FileName] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL
)
ON [PRIMARY]
GO

[David Portas]

Why would you want to create permanent tables at runtime? What is the
point have having multiple tables of the same structure? Why not just
add a user_name column to this table structure and then have all users
point to the same table?

The way to do what you have asked for is to use dynamic SQL. There are
lots of reasons why that is a really bad idea but then creating tables
at runtime seems like a pretty bad idea already!

See: http://www.sommarskog.se/dynamic_sql.html
for more info.

P.S. You have used the prefix sp_ for your stored procedure. sp_ is
reserved for system stored procedures. It has special meaning and
should not be used except for procs in Master.

--
David Portas
SQL Server MVP
--

[Rainer.Schwenkreis]

Hello,

Michael Jackson wrote:

I have a stored procedure to create a table. In my program I want the user
to name the table to be created, therefore I pass a parameter to the SP for
the table name. I cannot get it to work.

It creates a table called "@NewTableName" .

Any ideas?

CREATE PROCEDURE dbo.sp_FFProduc tion_CreateTabl e
(
@NewTableName nvarchar(128)
)
AS
CREATE TABLE [dbo].[@NewTableName]
(
[ProductionID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductionName] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[ProductionDate] [datetime] NOT NULL ,
[DocumentID] [int] NOT NULL,
[FileName] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL
)
ON [PRIMARY]
GO


The only way to create a dynamically named table is using EXECUTE or
sp_executesql stored procedure.
CREATE PROCEDURE dbo.sp_FFProduc tion_CreateTabl e
(
@NewTableName nvarchar(128)
)
AS
EXECUTE ('CREATE TABLE [dbo].[' + @NewTableName + ']
(
[ProductionID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductionName] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[ProductionDate] [datetime] NOT NULL ,
[DocumentID] [int] NOT NULL,
[FileName] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL )
ON [PRIMARY]'
GO

But these methods have the disadvantage, that the user calling the
SP must have the create table privilege or in your case must be member
of the db_owner role.

You should avoid creating tables, if this is possible in your
application. For example you could use an additional column in your table.
Rainer

[Michael Jackson]

Thanks for the help.
A table has to be produced which can be 'bundled up" with related files and
given to a client. Each table stands alone and cannot contain info from
other process.
"David Portas" <RE************ *************** *@acm.org> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.com...

Why would you want to create permanent tables at runtime? What is the
point have having multiple tables of the same structure? Why not just
add a user_name column to this table structure and then have all users
point to the same table?

The way to do what you have asked for is to use dynamic SQL. There are
lots of reasons why that is a really bad idea but then creating tables
at runtime seems like a pretty bad idea already!

See: http://www.sommarskog.se/dynamic_sql.html
for more info.

P.S. You have used the prefix sp_ for your stored procedure. sp_ is
reserved for system stored procedures. It has special meaning and
should not be used except for procs in Master.

--
David Portas
SQL Server MVP
--

[David Portas]

In that case I suggest that sp_rename would be an eaiser method than
dynamic SQL.

CREATE TABLE new_table (...

EXEC sp_rename 'new_table', @NewTableName, 'OBJECT'

Or consider using DTS.

--
David Portas
SQL Server MVP
--