By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,301 Members | 3,690 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,301 IT Pros & Developers. It's quick & easy.

SP To create table

P: n/a
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_FFProduction_CreateTable
(
@NewTableName nvarchar(128)
)
AS
CREATE TABLE [dbo].[@NewTableName]
(
[ProductionID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductionName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ProductionDate] [datetime] NOT NULL ,
[DocumentID] [int] NOT NULL,
[FileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
ON [PRIMARY]
GO
Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
--

Jul 23 '05 #2

P: n/a
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_FFProduction_CreateTable
(
@NewTableName nvarchar(128)
)
AS
CREATE TABLE [dbo].[@NewTableName]
(
[ProductionID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductionName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ProductionDate] [datetime] NOT NULL ,
[DocumentID] [int] NOT NULL,
[FileName] [nvarchar] (255) COLLATE SQL_Latin1_General_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_FFProduction_CreateTable
(
@NewTableName nvarchar(128)
)
AS
EXECUTE ('CREATE TABLE [dbo].[' + @NewTableName + ']
(
[ProductionID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductionName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProductionDate] [datetime] NOT NULL ,
[DocumentID] [int] NOT NULL,
[FileName] [nvarchar] (255) COLLATE SQL_Latin1_General_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

Jul 23 '05 #3

P: n/a
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*********************@g14g2000cwa.googlegro ups.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
--

Jul 23 '05 #4

P: n/a
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
--

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.