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

Creating a dynamic Global Temp Table within a stored procedure

P: n/a
hi,

I wish to create a temporary table who's name is dynamic based on the
argument.

ALTER PROCEDURE [dbo].[generateTicketTable]

@PID1 VARCHAR(50),
@PID2 VARCHAR(50),
@TICKET VARCHAR(20)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @DATA XML

SET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)
CREATE TABLE ##@TICKET (DATA XML)

INSERT INTO ##@TICKET VALUES(@DATA)

END
is what i have so far - although it just creates a table with a name of
##@TICKET - which isn't what i want. I want it to evaluate the name.

any ideas?

Sep 7 '06 #1
Share this Question
Share on Google+
16 Replies


P: n/a
Am 6 Sep 2006 22:14:18 -0700 schrieb pu*******@gmail.com:
hi,

I wish to create a temporary table who's name is dynamic based on the
argument.

ALTER PROCEDURE [dbo].[generateTicketTable]

@PID1 VARCHAR(50),
@PID2 VARCHAR(50),
@TICKET VARCHAR(20)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @DATA XML

SET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)
CREATE TABLE ##@TICKET (DATA XML)

INSERT INTO ##@TICKET VALUES(@DATA)

END
is what i have so far - although it just creates a table with a name of
##@TICKET - which isn't what i want. I want it to evaluate the name.

any ideas?
Use EXECUTE for the create statement:
....
EXEC('CREATE TABLE ##' + @TICKET + ' (DATA XML)')
....

bye, Helmut
Sep 7 '06 #2

P: n/a
(pu*******@gmail.com) writes:
I wish to create a temporary table who's name is dynamic based on the
argument.
Why? While you can do as Helmut suggest, the idea does not appear sound
at all. If you explain your real business problem, we might be able to
suggest a better solution.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 7 '06 #3

P: n/a
>I wish to create a temporary table whose name is dynamically based on the argument sic: parameter]. <<

Not in an RDBMS! You missed the basic concepts of what a table is. It
models an entity or relationship in a real world. To have tables
created on the fly is to have a world in which elephants or any other
entity at all can fall from the sky.

Next, using temp tables of any kind is a sign of poor SQL programming.
This is just like hanging a scratch tape in a 1950's file system, so
you can do a sequence of procedural steps instead of using declarative
programming.

What is the REAL problem? Don't tell us what kind of kludge you want.

Sep 7 '06 #4

P: n/a

--CELKO-- wrote:
I wish to create a temporary table whose name is dynamically based on the argument sic: parameter]. <<

Not in an RDBMS! You missed the basic concepts of what a table is. It
models an entity or relationship in a real world. To have tables
created on the fly is to have a world in which elephants or any other
entity at all can fall from the sky.
There are a lot of reasons someone might want to create a table on the
fly. They might want to store a snapshot of data at a given time that
is easily accessable. It may not fit your ideal of how SQL "should" be
used, but it's actually quite common in the real world - and it need
not invoke images of falling pachyderms.
Next, using temp tables of any kind is a sign of poor SQL programming.
This is just like hanging a scratch tape in a 1950's file system, so
you can do a sequence of procedural steps instead of using declarative
programming.
Sometimes it's just convenient to make a temp table. I'd wager most
people don't particularly care that it bothers your delicate
sensibilities.
What is the REAL problem? Don't tell us what kind of kludge you want.
He told you what he wanted - the "problem" is your inability to
understand that most people use SQL in ways that go beyond your
precious standard.

Sep 7 '06 #5

P: n/a
>There are a lot of reasons someone might want to create a table on the fly. They might want to store a snapshot of data at a given time that is easily accessable. <<
>It may not fit your ideal of how SQL "should" be used, but it's actually quite common in the real world - and it need not invoke images of falling pachyderms. <<
Good RDBMS programming and principles are not my personal opinion; you
can read Dr. Codd, Chris Date, Fabian Pascal, McGovern, etc. for the
foundations. Erland just told the guy the same thing, but without any
details to help him learn. Why not jump him and rant?
>Sometimes it's just convenient to make a temp table. <<
Yes, it is. It is also just convenient to use a copper penny instead
of getting a fuse from the hardware store.
>He told you what he wanted - the "problem" is your inability to understand that most people use SQL in ways that go beyond your precious standard. <<
No, there was no problem statement. As Chris Date puts it in a book
title of his, we need to hear "WHAT" and not "HOW" to have a spec.
Also, I think that after seven books on the language and ten years on
the Standards Committee, I have some understanding of SQL :) What are
your credentials?

A problem statement is "my bill of materials is a hierachy and I need
to aggregate the weights of the components into the sub-assemblies"; it
is not "How do I create a temp table {assumed method} to aggregate
the weights of the components into the sub-assemblies"; that is a
method and not a result. Now we can come back with the nested sets
model, or adjacency list model with a recursive CTE as a suggestion for
the hierarchy, and avoid a temp table completely. Ask "WHAT" and not
"HOW" -- basic software engineering. Hell, basic any kind of
engineering!

Sep 7 '06 #6

P: n/a

--CELKO-- wrote:
There are a lot of reasons someone might want to create a table on the fly. They might want to store a snapshot of data at a given time that is easily accessable. <<
It may not fit your ideal of how SQL "should" be used, but it's actually quite common in the real world - and it need not invoke images of falling pachyderms. <<

Good RDBMS programming and principles are not my personal opinion; you
can read Dr. Codd, Chris Date, Fabian Pascal, McGovern, etc. for the
foundations. Erland just told the guy the same thing, but without any
details to help him learn. Why not jump him and rant?
I've read from all of those guys - but the fact of the matter is, at
the end of the day business needs what business needs. There is
nothing wrong with creating a temp table if you need to create a temp
table.
Sometimes it's just convenient to make a temp table. <<

Yes, it is. It is also just convenient to use a copper penny instead
of getting a fuse from the hardware store.
The difference is that a putting a penny in your fuse box can burn your
house down; whereas creating a temp table will not. You might run into
problems down the road if you're misusing temp tables, but that is a
different issue altogether.
He told you what he wanted - the "problem" is your inability to understand that most people use SQL in ways that go beyond your precious standard. <<

No, there was no problem statement.
He asked how to create a dynamically named table.
As Chris Date puts it in a book
title of his, we need to hear "WHAT" and not "HOW" to have a spec.
And if we were getting specs for a project that would be a valid point
- we're not, so it isn't. He asked a simple question about how to do
something the language clearly supports doing.
Also, I think that after seven books on the language and ten years on
the Standards Committee, I have some understanding of SQL :)
Maybe, maybe not - I've read a lot of pretty bad technical books :b
What are your credentials?
I stayed at a Holiday Inn Express.
A problem statement is "my bill of materials is a hierachy and I need
to aggregate the weights of the components into the sub-assemblies"; it
is not "How do I create a temp table {assumed method} to aggregate
the weights of the components into the sub-assemblies"; that is a
method and not a result.
His problem is that he wanted to make a table with a dynamic name and
didn't know how. YOUR problem is that you're an arrogant,
self-important ass who uses usenet to bash people from behind your
keyboard.
Now we can come back with the nested sets
model, or adjacency list model with a recursive CTE as a suggestion for
the hierarchy, and avoid a temp table completely.
Or, we could realize that he's probably working on something right now
and needs an answer to his rather simple question. We could point out
to him that there are more standard ways of doing what he's doing, and
if he cares we could point him towards those ways.
Ask "WHAT" and not
"HOW" -- basic software engineering. Hell, basic any kind of
engineering!
Maybe if you have time you might look into basic human interaction.

Sep 7 '06 #7

P: n/a
(cp****@gmail.com) writes:
There are a lot of reasons someone might want to create a table on the
fly. They might want to store a snapshot of data at a given time that
is easily accessable. It may not fit your ideal of how SQL "should" be
used, but it's actually quite common in the real world - and it need
not invoke images of falling pachyderms.
Yes, temp tables are a fair game, but dynamically named tables isn't.
If nothing else, because it's completely painful to work with them.

Reading the original post again, I think the real solution that Pukivruki
needs is a permanent table keyed by a process id. Often @@spid can be
used as key, but in this case @TICKET may be a better choice.

See http://www.sommarskog.se/share_data.html#prockeyed for some more
discussion on the idea.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 7 '06 #8

P: n/a

Erland Sommarskog wrote:
(cp****@gmail.com) writes:
There are a lot of reasons someone might want to create a table on the
fly. They might want to store a snapshot of data at a given time that
is easily accessable. It may not fit your ideal of how SQL "should" be
used, but it's actually quite common in the real world - and it need
not invoke images of falling pachyderms.

Yes, temp tables are a fair game, but dynamically named tables isn't.
If nothing else, because it's completely painful to work with them.

Reading the original post again, I think the real solution that Pukivruki
needs is a permanent table keyed by a process id. Often @@spid can be
used as key, but in this case @TICKET may be a better choice.
Well, personally I agree with you for his case - but there is a
difference between telling someone there is a better way of doing
something, and getting all in their face with this "Not in an RDBMS!"
nonsense. There is a difference between trying to help someone out,
and trying to use the newsgroup as a means to work out your personal
insecurities by berating someone who asks a simple question.

Plus, I'm just bored today so... :b

Sep 7 '06 #9

P: n/a
I would like to comment that in my opinion MS SQL Server 2005 is not -
repeat, not - an RDBMS. Not anymore. It is much much more these days.
That's what the market dictates.

Sep 7 '06 #10

P: n/a
Erland Sommarskog wrote:
(cp****@gmail.com) writes:
>There are a lot of reasons someone might want to create a table on the
fly. They might want to store a snapshot of data at a given time that
is easily accessable. It may not fit your ideal of how SQL "should" be
used, but it's actually quite common in the real world - and it need
not invoke images of falling pachyderms.

Yes, temp tables are a fair game, but dynamically named tables isn't.
If nothing else, because it's completely painful to work with them.
Following this from an Oracle perspective I am horrified at the use
of these create-a-table on-the-fly methodologies and was hoping that
the changes in SQL Server 2005 would render them a thing of the past.

One question about them though ... how does the optimizer deal with
them?

Thanks.
--
Daniel Morgan
University of Washington
Sep 8 '06 #11

P: n/a
cp****@gmail.com wrote:
Or, we could realize that he's probably working on something right now
and needs an answer to his rather simple question. We could point out
to him that there are more standard ways of doing what he's doing, and
if he cares we could point him towards those ways.
The time to tell the newbie "don't do that" is /now/ - not two years
later, after the bad method has spent two years getting itself
entrenched in the system.

Original Poster, if you're still reading this thread: it would almost
certainly be better to use a table with a static name, moving the
dynamic portion to a column within that table, e.g.

-- ##TICKETS has structure (TICKET VARCHAR(20), DATA XML)

INSERT INTO ##TICKETS (TICKET, DATA) VALUES (@TICKET, @DATA)

I'm not familiar with global temp tables (I've learned a little by
Googling today) so I'll let someone else address their pros/cons.

Speaking of Googling, this seems particularly relevant:

http://www.sommarskog.se/dynamic_sql.html#Cre_tbl
Sep 8 '06 #12

P: n/a

"DA Morgan" <da******@psoug.orgwrote in message
news:11***************@bubbleator.drizzle.com...
Erland Sommarskog wrote:
> (cp****@gmail.com) writes:
>>There are a lot of reasons someone might want to create a table on the
fly. They might want to store a snapshot of data at a given time that
is easily accessable. It may not fit your ideal of how SQL "should" be
used, but it's actually quite common in the real world - and it need
not invoke images of falling pachyderms.

Yes, temp tables are a fair game, but dynamically named tables isn't.
If nothing else, because it's completely painful to work with them.

Following this from an Oracle perspective I am horrified at the use
of these create-a-table on-the-fly methodologies and was hoping that
the changes in SQL Server 2005 would render them a thing of the past.
Yes, but why should a new version eliminate bad programmers?
One question about them though ... how does the optimizer deal with
them?

Thanks.
--
Daniel Morgan
University of Washington

Sep 8 '06 #13

P: n/a
DA Morgan (da******@psoug.org) writes:
Following this from an Oracle perspective I am horrified at the use
of these create-a-table on-the-fly methodologies and was hoping that
the changes in SQL Server 2005 would render them a thing of the past.
Nah, there is indeed less need for temp tables these days. The addition
of derived tables in SQL 6.5 helped a lot.

But there are certainly cases where they still come in handy. If nothing
else, they make development simpler as you can use them as a work area.
And there are still situations where they can help performance. Say that
you have:

WITH CTE AS (SELECT ...)
SELECT CTE
JOIN CTE
WHERE NOT EXISTS (SELECT * FROM CTE ...)

While this syntax is neat and pure, the full story is that SQL Server
currently always compute the CTE for each instance. Thus, if the CTE is
complex, it may be a good idea to get the data into a temp table or
a table variable first.
One question about them though ... how does the optimizer deal with
them?
A temp table has stastistics like any regular table. If sufficiently many
rows changes, that will trigger a recompilation. This can be both a
blessing and a curse, not the least in SQL 2000 where recompilation
always hits the entire procedure.

Table variables does not have statistics, so for them the optimizer
will have to make standard assumptions.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 8 '06 #14

P: n/a
Ed Murphy (em*******@socal.rr.com) writes:
Original Poster, if you're still reading this thread: it would almost
certainly be better to use a table with a static name, moving the
dynamic portion to a column within that table, e.g.

-- ##TICKETS has structure (TICKET VARCHAR(20), DATA XML)

INSERT INTO ##TICKETS (TICKET, DATA) VALUES (@TICKET, @DATA)

I'm not familiar with global temp tables (I've learned a little by
Googling today) so I'll let someone else address their pros/cons.
Global temp tables is a fairly hopeless feature. Since the table is
visible to all processes, you need to make the name unique in some
other way, and this leads to a mess with dynamic SQL.

When I worked on my article for sharing data between stored procedures,
http://www.sommarskog.se/share_data.html, the idea occurred to me that
global temp tables may be could be used for this. But this a global temp
table goes away when the processes that created it goes away, it was
difficult to make anything useful of it. (If another process has an
active reference to the temp table, when the creating process exits,
the global temp table hangs around until the other process has completed
its query.)

Neverheless, I tipped a colleague to use a global temp table recently.
He was working with an export job, and ran into some problem with
the queryout option in BCP. Instead he dumped his data into a global
temp table, which he then could access from BCP. But his situation
was such that he could assume that there not two instances of the
job at the same time, so he did not need to have a dynamic name.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 8 '06 #15

P: n/a
PRACTICE WHAT YOU TEACH CELKO!!!!!

I've seen you use a) TEMPORARAY TABLES and b) CURSORS in these forums.

Its yet another case where your biased 'education instiutions only' detaches
you from what people really need and want out in the real world where we
have real problems and require real solutions....

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.netwrote in message
news:11********************@e3g2000cwe.googlegroup s.com...
>>There are a lot of reasons someone might want to create a table on the
fly. They might want to store a snapshot of data at a given time that
is easily accessable. <<
>>It may not fit your ideal of how SQL "should" be used, but it's actually
quite common in the real world - and it need not invoke images of
falling pachyderms. <<

Good RDBMS programming and principles are not my personal opinion; you
can read Dr. Codd, Chris Date, Fabian Pascal, McGovern, etc. for the
foundations. Erland just told the guy the same thing, but without any
details to help him learn. Why not jump him and rant?
>>Sometimes it's just convenient to make a temp table. <<

Yes, it is. It is also just convenient to use a copper penny instead
of getting a fuse from the hardware store.
>>He told you what he wanted - the "problem" is your inability to
understand that most people use SQL in ways that go beyond your precious
standard. <<

No, there was no problem statement. As Chris Date puts it in a book
title of his, we need to hear "WHAT" and not "HOW" to have a spec.
Also, I think that after seven books on the language and ten years on
the Standards Committee, I have some understanding of SQL :) What are
your credentials?

A problem statement is "my bill of materials is a hierachy and I need
to aggregate the weights of the components into the sub-assemblies"; it
is not "How do I create a temp table {assumed method} to aggregate
the weights of the components into the sub-assemblies"; that is a
method and not a result. Now we can come back with the nested sets
model, or adjacency list model with a recursive CTE as a suggestion for
the hierarchy, and avoid a temp table completely. Ask "WHAT" and not
"HOW" -- basic software engineering. Hell, basic any kind of
engineering!

Sep 10 '06 #16

P: n/a
>Following this from an Oracle perspective I am horrified at the use of these create-a-table on-the-fly methodologies <<

Is it just a bad sample on my part, or do Oracle programmers tend to
use a lot of cursors and procedural code compared to other SQL products?

Sep 10 '06 #17

This discussion thread is closed

Replies have been disabled for this discussion.