dynamically creating temp table names

[Billy Cormic]

Hello,
I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000.

For example:

DECLARE @l_personsUID int

select @l_personsUID = 9842

create table ##Test1table /*then the @l_personsUID */
(
resultset1 int


)

The key to the problem is that I want to use the variable
@l_personsUID to name then temp table. The name of the temp table
should be ##Test1table984 2 not ##Test1table.

Thanks for you help.

Billy

[Greg D. Moore \(Strider\)]

Re: dynamically creating temp table names


"Billy Cormic" <billy_cormic@h otmail.com> wrote in message
news:dd2f7565.0 311251937.cf18c f9@posting.goog le.com...[color=blue]
> Hello,
> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000.
>
> For example:
>
> DECLARE @l_personsUID int
>
> select @l_personsUID = 9842
>
> create table ##Test1table /*then the @l_personsUID */
> (
> resultset1 int
>
>
> )
>
> The key to the problem is that I want to use the variable
> @l_personsUID to name then temp table. The name of the temp table
> should be ##Test1table984 2 not ##Test1table.
>[/color]

May I ask why?

You can probably do this by dynamically building the string.

But it's going to be messy.

[color=blue]
> Thanks for you help.
>
> Billy[/color]

[Simon Hayes]

Re: dynamically creating temp table names

billy_cormic@ho tmail.com (Billy Cormic) wrote in message news:<dd2f7565. 0311251937.cf18 cf9@posting.goo gle.com>...[color=blue]
> Hello,
> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000.
>
> For example:
>
> DECLARE @l_personsUID int
>
> select @l_personsUID = 9842
>
> create table ##Test1table /*then the @l_personsUID */
> (
> resultset1 int
>
>
> )
>
> The key to the problem is that I want to use the variable
> @l_personsUID to name then temp table. The name of the temp table
> should be ##Test1table984 2 not ##Test1table.
>
> Thanks for you help.
>
> Billy[/color]

You could use dynamic SQL, but that would not be a good solution. If
the table names are dynamic, then all code accessing the tables would
need to be dynamic also, and that will create a lot of issues.

A better approach would be to have a single, permanent table, with
personsUID as part of the key. See here for a good discussion of this
issue:

http://www.algonet.se/~sommar/dynamic_sql.html#Sales_yymm

Simon

[Billy Cormic]

Re: dynamically creating temp table names

I want to do this so that i can create individual tables to set as
datasources for certain crystal reports.

"Greg D. Moore \(Strider\)" <mooregr@greenm s.com> wrote in message news:<2jWwb.144 035$ji3.17559@t wister.nyroc.rr .com>...[color=blue]
> "Billy Cormic" <billy_cormic@h otmail.com> wrote in message
> news:dd2f7565.0 311251937.cf18c f9@posting.goog le.com...[color=green]
> > Hello,
> > I am interested in dynamically creating temp tables using a
> > variable in MS SQL Server 2000.
> >
> > For example:
> >
> > DECLARE @l_personsUID int
> >
> > select @l_personsUID = 9842
> >
> > create table ##Test1table /*then the @l_personsUID */
> > (
> > resultset1 int
> >
> >
> > )
> >
> > The key to the problem is that I want to use the variable
> > @l_personsUID to name then temp table. The name of the temp table
> > should be ##Test1table984 2 not ##Test1table.
> >[/color]
>
> May I ask why?
>
> You can probably do this by dynamically building the string.
>
> But it's going to be messy.
>
>[color=green]
> > Thanks for you help.
> >
> > Billy[/color][/color]

[--CELKO--]

Re: dynamically creating temp table names

>> I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000. <<

Learn to write correct SQL instead. The use of temp tables is usually
a sign of really bad code -- the temp tables are almost always used to
hold steps in a procedural solution instead of a having a set-oriented
non-proceudral solution. This also says that you have no data model
and that any user, present or future, can change it on the fly.

Oh, if you don't care about performance, portability, readability,
security, and all that other stuff, then you can use dynamic SQL to
screw up your application this way.

[Billy Cormic]

Re: dynamically creating temp table names

OK. I will just create anohter table... not a bunch of temp tables to
hold the results.

thanks

joe.celko@north face.edu (--CELKO--) wrote in message news:<a264e7ea. 0311261052.1209 8cb6@posting.go ogle.com>...[color=blue][color=green][color=darkred]
> >> I am interested in dynamically creating temp tables using a[/color][/color]
> variable in MS SQL Server 2000. <<
>
> Learn to write correct SQL instead. The use of temp tables is usually
> a sign of really bad code -- the temp tables are almost always used to
> hold steps in a procedural solution instead of a having a set-oriented
> non-proceudral solution. This also says that you have no data model
> and that any user, present or future, can change it on the fly.
>
> Oh, if you don't care about performance, portability, readability,
> security, and all that other stuff, then you can use dynamic SQL to
> screw up your application this way.[/color]