Create table with table name passed by proc !

Question

Post reply

Create table with table name passed by proc !

edward-837480

Say Hey Kid

Points: 682
More actions
August 1, 2008 at 10:19 pm

#190431

I need to create table with the data information passed under a proc as follows:
CREATE PROCEDURE [dbo].[_pXymd]
@Fname char(8)
AS
BEGIN
SET NOCOUNT ON
DECLARE @pk char(11)
SELECT @pk='PK_'+@Fname
CREATE TABLE [dbo].[@Fname] (
[ICno] [int] NOT NULL DEFAULT (0),
[Xdate] [smalldatetime] NOT NULL,
[Rem] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT ('')
) ON [PRIMARY]
ALTER TABLE [dbo].[@Fname] WITH NOCHECK ADD
CONSTRAINT [@PK] PRIMARY KEY CLUSTERED
(
[ICno],
[Xdate]
) ON [PRIMARY]
END
GO
=========================
When executing: EXEC _pXymd '_X080804'
The database can create a table named @Fname but not _X080804 that I want.
Can any expert help me to modify the codes to create the table with table name passed by Proc ?
Thanks so much.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 1

Put the table name in brackets.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

edward-837480 Say Hey Kid Points: 682 More actions · Answer 2

It can't work !!

A table named (@Fname) is created !

I change to codes as follows:

..

CREATE TABLE [dbo].[(@Fname)] (

..

Is the change correct ?

Thanks for your help.

Grant Fritchey SSC Guru Points: 398707 More actions · Answer 3

You'll have to use dynamic SQL. Put everything into a string and concatenate the table name.

Here's an example (uncompiled, so test it):

DECLARE @mystring nvarchar(max)

SET @mystring = 'CREATE TABLE dbo.' + @Table + ' (Col1 int)'

EXEC @mystring

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

edward-837480 Say Hey Kid Points: 682 More actions · Answer 4

Sorry to tell you it can't work.... :crying:

I issue the following codes :

DECLARE @ExStr varchar(500)

SET @ExStr = 'CREATE TABLE dbo._X080805 (

[ICno] [int] NOT NULL DEFAULT (0),

[Xdate] [smalldatetime] NOT NULL

) ON [PRIMARY] '

EXEC @ExStr

================ But get the following result :

Server: Msg 203, Level 16, State 2, Line 8

The name 'CREATE TABLE dbo._X080805 (

[ICno] [int] NOT NULL DEFAULT (0),

[Xdate] [smalldatetime] NOT NULL

) ON [PRIMARY] ' is not a valid identifier.

================

An alternate suggestion is welcome and thanks a lot for your help.

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 5

edward (8/2/2008)
Sorry to tell you it can't work.... :crying:
I issue the following codes :
DECLARE @ExStr varchar(500)
SET @ExStr = 'CREATE TABLE dbo._X080805 (
[ICno] [int] NOT NULL DEFAULT (0),
[Xdate] [smalldatetime] NOT NULL
) ON [PRIMARY] '
EXEC @ExStr
================ But get the following result :
Server: Msg 203, Level 16, State 2, Line 8
The name 'CREATE TABLE dbo._X080805 (
[ICno] [int] NOT NULL DEFAULT (0),
[Xdate] [smalldatetime] NOT NULL
) ON [PRIMARY] ' is not a valid identifier.
================
An alternate suggestion is welcome and thanks a lot for your help.

You need to use EXEC (@ExStr). Otherwise SQL SERVER will expect the value of @ExStr to be a stored procedure.

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 6

That's correct... I missed that little "nuance"...

The code needs to look like this... Note the parentheses around @ExStr in the EXEC statement.

DECLARE @ExStr varchar(500)

SET @ExStr = 'CREATE TABLE dbo._X080805 (

[ICno] [int] NOT NULL DEFAULT (0),

[Xdate] [smalldatetime] NOT NULL

) ON [PRIMARY] '

EXEC (@ExStr)

Since you were trying to make the table name variable, the following will work just fine... I added the brackets just to be on the safe side if @FName turns out to be a whacky name....

DECLARE @ExStr VARCHAR(8000),

@FName VARCHAR(128)

SET @FName = '_X080805'

SET @ExStr =

' CREATE TABLE dbo.['+@FName+'] (

[ICno] INT NOT NULL DEFAULT (0),

[Xdate] SMALLDATETIME NOT NULL)'

PRINT (@ExStr) --Just showing the SQL... comment out in production

EXEC (@ExStr)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

edward-837480 Say Hey Kid Points: 682 More actions · Answer 7

I achieve my goal now...:D

Many many thanks to all of you... 😛

Have a nice week-end like me ! :laugh: