Dynamically generate table name?

The code below is invalid. However, hopefully it will give a clue as to what
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
David Portas
SQL Server MVP
--|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegroups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?

>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.

>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
[url]http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx.[/url
]|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:

> The code below is invalid. However, hopefully it will give a clue as to wh
at
> I'm trying to achieve though - i.e. I want to create a new table which has
a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with nam
e
> "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.

>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated. :)
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...

> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing. :)

> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.

> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.

> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.

> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.