SP To create table

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

re: SP To create table

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
--

re: SP To create table

Hello,

Michael Jackson wrote:
[color=blue]
> 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
>
>[/color]

The only way to create a dynamically named table is using EXECUTE or
sp_executesql stored procedure.
[color=blue]
> 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[/color]
SQL_Latin1_General_CP1_CI_AS NOT[color=blue]
> NULL ,
> [ProductionDate] [datetime] NOT NULL ,
> [DocumentID] [int] NOT NULL,
> [FileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT[/color]
NULL[color=blue]
> )
> ON [PRIMARY]'
> GO
>[/color]



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

re: SP To create table

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" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:1109100834.390168.77840@g14g2000cwa.googlegro ups.com...[color=blue]
> 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
> --
>[/color]

re: SP To create table

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
--