Creating a Dynamic Temporary Table

When I execute the following Stored Procedure with a parameter (EXEC MyProc
'MyTab'); it creates a table ##MyTempTable. What happened to the parameter
@.MyTempTable that was passed in the SQL statement?
I meant to create the temp table named MyTab as passed to the SP from the
statement.
CREATE PROCEDURE [dbo].[MyProc] (@.MyTempTable nvarchar(50)) AS
SELECT *
INTO ##MyTempTable
FROM Customers
GOThe right answer is never pass a table name as a parameter. You need
to understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A
model of a set of entities or relationships. EACH TABLE SHOULD BE A
DIFFERENT KIND OF ENTITY. What having a generic procedure works
equally on automobiles, octopi or Britney Spear's discology is saying
that your application is a disaster of design.
1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.
2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember
the basics of Software Engineering? Modules need weak coupling and
strong cohesion, etc. This is far more fundamental than just SQL; it
has to do with learning to programming at all.
3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw. Look up the term
attribute splitting.
4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and applications
do not work at that level, if you want to have any data integrity.
Stop writing code like this. You are mimicking a 1950's scratch tape
file. But more than that, you never used the parameter anywhere in the
procedure -- nothing happened to it. Also, I see you used the
"Magical NVARCHAR(50)" data type. Do you really have names that long?
I doubt it. You will eventually get such a garbage name; I can give
you a Chinese Suttra if you want to do use it :)
Use the Customers table in your statements. Unfortunately, we have no
idea what you wanted to do, so nobody can help you further.|||Shariq,
I have a little confusion as to what you are attempting to achieve.
The short answer to what you asking is as follows:
CREATE STORED PROCEDURE [dbo].[usp_My_SProc]
@.MyTempTable nvarchar(50)=''
AS
If @.MyTempTable<>''
BEGIN
DECLARE @.sSTR varchar(2000)
SET @.sSTR = ' '
SET @.sSTR = @.sSTR + ' SELECT * INTO '
SET @.sSTR = @.sSTR + '#' + @.MyTempTable
SET @.sSTR = @.sSTR + ' FROM Customers '
EXEC (@.sSTR)
END
This will achieve the result your question poses, but does not a lot to
possibly achieve your intended goal.
The one drawback to the above code is that the EXEC(@.sSTR) command runs in
an independant thread from the Stored Procedure and such a table cannot be
accessed from another thread, unless you use the ## prefix, but this, again,
brings up it's own dilemmas as the same Stored Procedure acn only be run onc
e
at a time.
"Shariq" wrote:

> When I execute the following Stored Procedure with a parameter (EXEC MyPro
c
> 'MyTab'); it creates a table ##MyTempTable. What happened to the parameter
> @.MyTempTable that was passed in the SQL statement?
> I meant to create the temp table named MyTab as passed to the SP from the
> statement.
> CREATE PROCEDURE [dbo].[MyProc] (@.MyTempTable nvarchar(50)) AS
> SELECT *
> INTO ##MyTempTable
> FROM Customers
> GO
>|||Tony,
Thanks for you help; the code you provided is exactly what I was looking for
.
"Tony Scott" wrote:
> Shariq,
> I have a little confusion as to what you are attempting to achieve.
> The short answer to what you asking is as follows:
> CREATE STORED PROCEDURE [dbo].[usp_My_SProc]
> @.MyTempTable nvarchar(50)=''
> AS
> If @.MyTempTable<>''
> BEGIN
> DECLARE @.sSTR varchar(2000)
> SET @.sSTR = ' '
> SET @.sSTR = @.sSTR + ' SELECT * INTO '
> SET @.sSTR = @.sSTR + '#' + @.MyTempTable
> SET @.sSTR = @.sSTR + ' FROM Customers '
> EXEC (@.sSTR)
> END
> This will achieve the result your question poses, but does not a lot to
> possibly achieve your intended goal.
> The one drawback to the above code is that the EXEC(@.sSTR) command runs in
> an independant thread from the Stored Procedure and such a table cannot be
> accessed from another thread, unless you use the ## prefix, but this, agai
n,
> brings up it's own dilemmas as the same Stored Procedure acn only be run o
nce
> at a time.
>
> "Shariq" wrote:
>

Creating a dynamic temporary table

I need to dynamically create a temporary table like this (I don't know its
structure until runtime - it is based on selections made by user):
DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 + 'varchar(250)
+ ', ' + @.col2 + 'varchar2(250))'
EXEC(@.CreateStatement)
--following is code to fill this table
The table created is not accessible after the line EXEC(@.CreateStatement). I
know this (temp table have a scope limited to the stored procedure that
created them).
Is there another way to accomplish this? I also tried using table variables,
but I wasn't able to make a stored procedure that returns a table variable.YOU CAN USE FUNCTION INSTEAD OF STORED PROC
CREATE FUNCTION TEMP (@.col1 varchar(250) ,@.col2 varchar(250))
RETURNS TABLE
as
RETURN SELECT @.col1+','+@.col2 as TEXT
"razdanro" wrote:
> I need to dynamically create a temporary table like this (I don't know its
> structure until runtime - it is based on selections made by user):
> DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 + 'varchar(25
0)
> + ', ' + @.col2 + 'varchar2(250))'
> EXEC(@.CreateStatement)
> --following is code to fill this table
> The table created is not accessible after the line EXEC(@.CreateStatement).
I
> know this (temp table have a scope limited to the stored procedure that
> created them).
> Is there another way to accomplish this? I also tried using table variable
s,
> but I wasn't able to make a stored procedure that returns a table variable.[/color
]|||You can create tempdb..temptable or create ##temptable and it will be
available until it is dropped or the sql server is re-booted.. The
difference between #temptable and ##temptable
#temptable is a non-sharable connection specific temporary table. It goes
away when the SP (if created in an sp) or connection goes away..
##temptable can be seen by all spids, and lives until you drop it or the
server is re-booted... if you are wiriting for mutliple concurrnet users,
you may have to check for its existence before creating it... or come up
with some unque name, and/or attach a spid to separate your rows from those
inserted by another spid.
hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"razdanro" <razdanro@.discussions.microsoft.com> wrote in message
news:8F211B2B-42BE-4608-98EB-C4E350D64D8C@.microsoft.com...
> I need to dynamically create a temporary table like this (I don't know its
> structure until runtime - it is based on selections made by user):
> DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 +
'varchar(250)
> + ', ' + @.col2 + 'varchar2(250))'
> EXEC(@.CreateStatement)
> --following is code to fill this table
> The table created is not accessible after the line EXEC(@.CreateStatement).
I
> know this (temp table have a scope limited to the stored procedure that
> created them).
> Is there another way to accomplish this? I also tried using table
variables,
> but I wasn't able to make a stored procedure that returns a table
variable.|||create the table first using a hard coded "create table" statement (with at
least one column - a dummy column if needed) - then dynamically alter its
structure.
"razdanro" <razdanro@.discussions.microsoft.com> wrote in message
news:8F211B2B-42BE-4608-98EB-C4E350D64D8C@.microsoft.com...
> I need to dynamically create a temporary table like this (I don't know its
> structure until runtime - it is based on selections made by user):
> DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 +
'varchar(250)
> + ', ' + @.col2 + 'varchar2(250))'
> EXEC(@.CreateStatement)
> --following is code to fill this table
> The table created is not accessible after the line EXEC(@.CreateStatement).
I
> know this (temp table have a scope limited to the stored procedure that
> created them).
> Is there another way to accomplish this? I also tried using table
variables,
> but I wasn't able to make a stored procedure that returns a table
variable.|||Do not write SQL this way.
Temporary tables tell us that you are really writing procedural code
and have not learned to think in sets and declarative code yet. A temp
table is a "scratch tape" for an algorithm based on procedural steps in
95% of the cases. You probably should be using derived tables or
VIEWs.
Dynamic SQL tell us that you do not know what you are doing, so you
have to let a random stranger create a table in your data model at the
last minute.
Using over-sized VARCHAR(n) values tells us that you did no research to
find the proper size, but just grabbed a large dummy value. This also
means that you have no data model and probalby no data dictionary.
Finally, you will never learn SQL this way. You have already decided
on HOW you want to solve a problem. So people will show you how to
write kludges for your bad solution. But if you had posted WHAT you
want to do, then you might get a relational answer.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Let's try again with the actual problem.|||Here is the data model that I didn't design, but I have to work with right n
ow.
I have three entities: Companies, Sites and Contacts. A Company has 0 or
more Sites, a Site has 0 or more Contacts.
The application must allow users to add properties of these entities
dynamically.
These Properties are held in a table, and the values allowed are in a
PropertyValues table. There is also a table EntityProperty which is an
intersection table between Entities and PropertyValues.
So I have a design that actually stores data and metadata.
And now I have to make a SQLBuilder based on these Entities. I don't know
what Property will be selected as output, that's why I need to create a
dynamic temporary table to return the result.

Creating a common table expression--temporary table--using TSQL??

Using SQL against a DB2 table the 'with' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL 'with' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:

>Using SQL against a DB2 table the 'with' key word is used to
>dynamically create a temporary table with an SQL statement that is
>retained for the duration of that SQL statement.
>What is the equivalent to the SQL 'with' using TSQL? If there is not
>one, what is the TSQL solution to creating a temporary table that is
>associated with an SQL statement? Examples would be appreciated.
>Thank you!!

I believe there is such a thing in SQL Server 2005, but not in any earlier
versions.|||You did not say what version of MSSQL you are on so I will assume 2000.
This is straight from the TSQL books.

Temporary Tables
SQL Server supports temporary tables. These tables have names that
start with a number sign (#). If a temporary table is not dropped when
a user disconnects, SQL Server automatically drops the temporary table.
Temporary tables are not stored in the current database; they are
stored in the tempdb system database.
There are two types of temporary tables:
Local temporary tables
The names of these tables begin with one number sign (#). These tables
are visible only to the connection that created them.
Global temporary tables
The names of these tables begin with two number signs (##). These
tables are visible to all connections. If the tables are not dropped
explicitly before the connection that created them disconnects, they
are dropped as soon as all other tasks stop referencing them. No new
tasks can reference a global temporary table after the connection that
created it disconnects. The association between a task and a table is
always dropped when the current statement completes executing;
therefore, global temporary tables are usually dropped soon after the
connection that created them disconnects.
Many traditional uses of temporary tables can now be replaced with
variables that have the table data type.

Example
create table #TempTable (col1 varchar(10), col2 bit)
insert into #TempTable values('asdf', 1)
select * from #TempTable
select * into #TempTable2 from #TempTable
select * from #TempTable2
drop table #TempTable
drop table #TempTable2

You can just about anything with a temp table that you can with a
normal table, including indexes.

HTH
Paul|||Yes, but I think he wanted to associate the statement with the name, a
physical table - like a temporary view.

On 28 Dec 2004 07:19:13 -0800, "Paul" <stpaul_71@.yahoo.com> wrote:

>You did not say what version of MSSQL you are on so I will assume 2000.
>This is straight from the TSQL books.
>Temporary Tables
>SQL Server supports temporary tables. These tables have names that
>start with a number sign (#). If a temporary table is not dropped when
>a user disconnects, SQL Server automatically drops the temporary table.
>Temporary tables are not stored in the current database; they are
>stored in the tempdb system database.
>There are two types of temporary tables:
>Local temporary tables
>The names of these tables begin with one number sign (#). These tables
>are visible only to the connection that created them.
>Global temporary tables
>The names of these tables begin with two number signs (##). These
>tables are visible to all connections. If the tables are not dropped
>explicitly before the connection that created them disconnects, they
>are dropped as soon as all other tasks stop referencing them. No new
>tasks can reference a global temporary table after the connection that
>created it disconnects. The association between a task and a table is
>always dropped when the current statement completes executing;
>therefore, global temporary tables are usually dropped soon after the
>connection that created them disconnects.
>Many traditional uses of temporary tables can now be replaced with
>variables that have the table data type.
>
>
>Example
>create table #TempTable (col1 varchar(10), col2 bit)
>insert into #TempTable values('asdf', 1)
>select * from #TempTable
>select * into #TempTable2 from #TempTable
>select * from #TempTable2
>drop table #TempTable
>drop table #TempTable2
>
>You can just about anything with a temp table that you can with a
>normal table, including indexes.
>
>HTH
>Paul|||On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:

>Using SQL against a DB2 table the 'with' key word is used to
>dynamically create a temporary table with an SQL statement that is
>retained for the duration of that SQL statement.
>What is the equivalent to the SQL 'with' using TSQL? If there is not
>one, what is the TSQL solution to creating a temporary table that is
>associated with an SQL statement? Examples would be appreciated.
>Thank you!!

Hi Randi,

I don't know if it's exactly the same as the DB2 version (probably not),
but SQL Server supports derived table expressions. Example (from BOL):

USE pubs
GO
SELECT ST.stor_id, ST.stor_name
FROM stores AS ST,
(SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
FROM sales
GROUP BY stor_id
) AS SA
WHERE ST.stor_id = SA.stor_id
AND SA.title_count = (SELECT COUNT(*) FROM titles)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
> >Using SQL against a DB2 table the 'with' key word is used to
> >dynamically create a temporary table with an SQL statement that is
> >retained for the duration of that SQL statement.
> >What is the equivalent to the SQL 'with' using TSQL? If there is not
> >one, what is the TSQL solution to creating a temporary table that is
> >associated with an SQL statement? Examples would be appreciated.
> >Thank you!!
> Hi Randi,
> I don't know if it's exactly the same as the DB2 version (probably not),
> but SQL Server supports derived table expressions. Example (from BOL):
> USE pubs
> GO
> SELECT ST.stor_id, ST.stor_name
> FROM stores AS ST,
> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
> FROM sales
> GROUP BY stor_id
> ) AS SA
> WHERE ST.stor_id = SA.stor_id
> AND SA.title_count = (SELECT COUNT(*) FROM titles)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Hi Hugo, a common table expression, provided by the WITH
clause, is defined in Standard SQL (beginning with SQL:1999)
and is implemented in SQL Server 2005. Semantically, the WITH
clause is similar to defining one or more views whose scope and
extent is the enclosed query. Factoring out and naming these
common subexpressions in a query is meant to aid readability,
conciseness, maintainability, and even efficiency. There are cases
when a derived table is a perfectly good alternative, however,
when that derived table is used multiple times in the query a
common table expression becomes handy.

Taking the BOL example from above, imagine you wanted to
rank stores in decreasing order by number of distinct titles. Using
WITH, one could write (admittedly, in this case a view is a reasonable
choice too):

WITH DistinctTitles (stor_id, title_count) AS
(SELECT stor_id, COUNT(DISTINCT title_id)
FROM sales
GROUP BY stor_id)
SELECT T1.stor_id, T1.title_count,
COUNT(DISTINCT T2.title_count) AS stor_rank
FROM DistinctTitles AS T1
INNER JOIN
DistinctTitles AS T2
ON T2.title_count >= T1.title_count
GROUP BY T1.stor_id, T1.title_count;

It's also through the WITH clause that we can define recursive queries. This
is where WITH truly shines.

--
JAG|||Hi John,

Thanks for your explanation. I had heard that WITH would be introduced in
SQL Server 2005; unfortunately, I'll have to wait a little longer before
I'll get a chance to actually play with it. (I don't have a spare system
lying around that I can use to safely toy with beta software).

It does look promising, though. I'm sure I'll really get to like this
feature once I have it available!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <jag@.acm.org> wrote:

>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
>> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
>>
>> >Using SQL against a DB2 table the 'with' key word is used to
>> >dynamically create a temporary table with an SQL statement that is
>> >retained for the duration of that SQL statement.
>> >What is the equivalent to the SQL 'with' using TSQL? If there is not
>> >one, what is the TSQL solution to creating a temporary table that is
>> >associated with an SQL statement? Examples would be appreciated.
>> >Thank you!!
>>
>> Hi Randi,
>>
>> I don't know if it's exactly the same as the DB2 version (probably not),
>> but SQL Server supports derived table expressions. Example (from BOL):
>>
>> USE pubs
>> GO
>> SELECT ST.stor_id, ST.stor_name
>> FROM stores AS ST,
>> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
>> FROM sales
>> GROUP BY stor_id
>> ) AS SA
>> WHERE ST.stor_id = SA.stor_id
>> AND SA.title_count = (SELECT COUNT(*) FROM titles)
>>
>> Best, Hugo
>> --
>>
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>Hi Hugo, a common table expression, provided by the WITH
>clause, is defined in Standard SQL (beginning with SQL:1999)
>and is implemented in SQL Server 2005. Semantically, the WITH
>clause is similar to defining one or more views whose scope and
>extent is the enclosed query. Factoring out and naming these
>common subexpressions in a query is meant to aid readability,
>conciseness, maintainability, and even efficiency. There are cases
>when a derived table is a perfectly good alternative, however,
>when that derived table is used multiple times in the query a
>common table expression becomes handy.
>Taking the BOL example from above, imagine you wanted to
>rank stores in decreasing order by number of distinct titles. Using
>WITH, one could write (admittedly, in this case a view is a reasonable
>choice too):
>WITH DistinctTitles (stor_id, title_count) AS
> (SELECT stor_id, COUNT(DISTINCT title_id)
> FROM sales
> GROUP BY stor_id)
>SELECT T1.stor_id, T1.title_count,
> COUNT(DISTINCT T2.title_count) AS stor_rank
>FROM DistinctTitles AS T1
> INNER JOIN
> DistinctTitles AS T2
> ON T2.title_count >= T1.title_count
>GROUP BY T1.stor_id, T1.title_count;
>It's also through the WITH clause that we can define recursive queries. This
>is where WITH truly shines.

Just curious - can the scope of a WITH be more than one query? An entire
stored procedure, for instance?|||"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:h8q4t09uig7tn0lcro8ocjf6od0f6om854@.4ax.com...
> On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <jag@.acm.org> wrote:
> >"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> >news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
> >> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
> >>
> >> >Using SQL against a DB2 table the 'with' key word is used to
> >> >dynamically create a temporary table with an SQL statement that is
> >> >retained for the duration of that SQL statement.
> >> >What is the equivalent to the SQL 'with' using TSQL? If there is not
> >> >one, what is the TSQL solution to creating a temporary table that is
> >> >associated with an SQL statement? Examples would be appreciated.
> >> >Thank you!!
> >>
> >> Hi Randi,
> >>
> >> I don't know if it's exactly the same as the DB2 version (probably not),
> >> but SQL Server supports derived table expressions. Example (from BOL):
> >>
> >> USE pubs
> >> GO
> >> SELECT ST.stor_id, ST.stor_name
> >> FROM stores AS ST,
> >> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
> >> FROM sales
> >> GROUP BY stor_id
> >> ) AS SA
> >> WHERE ST.stor_id = SA.stor_id
> >> AND SA.title_count = (SELECT COUNT(*) FROM titles)
> >>
> >> Best, Hugo
> >> --
> >>
> >> (Remove _NO_ and _SPAM_ to get my e-mail address)
> >Hi Hugo, a common table expression, provided by the WITH
> >clause, is defined in Standard SQL (beginning with SQL:1999)
> >and is implemented in SQL Server 2005. Semantically, the WITH
> >clause is similar to defining one or more views whose scope and
> >extent is the enclosed query. Factoring out and naming these
> >common subexpressions in a query is meant to aid readability,
> >conciseness, maintainability, and even efficiency. There are cases
> >when a derived table is a perfectly good alternative, however,
> >when that derived table is used multiple times in the query a
> >common table expression becomes handy.
> >Taking the BOL example from above, imagine you wanted to
> >rank stores in decreasing order by number of distinct titles. Using
> >WITH, one could write (admittedly, in this case a view is a reasonable
> >choice too):
> >WITH DistinctTitles (stor_id, title_count) AS
> > (SELECT stor_id, COUNT(DISTINCT title_id)
> > FROM sales
> > GROUP BY stor_id)
> >SELECT T1.stor_id, T1.title_count,
> > COUNT(DISTINCT T2.title_count) AS stor_rank
> >FROM DistinctTitles AS T1
> > INNER JOIN
> > DistinctTitles AS T2
> > ON T2.title_count >= T1.title_count
> >GROUP BY T1.stor_id, T1.title_count;
> >It's also through the WITH clause that we can define recursive queries. This
> >is where WITH truly shines.
> Just curious - can the scope of a WITH be more than one query? An entire
> stored procedure, for instance?

No, a WITH clause encloses a single query expression and can be used
anywhere a query is used, e.g., in defining a view.

--
JAG|||On Wed, 29 Dec 2004 08:40:27 GMT, "John Gilson" <jag@.acm.org> wrote:

>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:h8q4t09uig7tn0lcro8ocjf6od0f6om854@.4ax.com...
>> On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <jag@.acm.org> wrote:
>>
>> >"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>> >news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
>> >> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
>> >>
>> >> >Using SQL against a DB2 table the 'with' key word is used to
>> >> >dynamically create a temporary table with an SQL statement that is
>> >> >retained for the duration of that SQL statement.
>> >> >What is the equivalent to the SQL 'with' using TSQL? If there is not
>> >> >one, what is the TSQL solution to creating a temporary table that is
>> >> >associated with an SQL statement? Examples would be appreciated.
>> >> >Thank you!!
>> >>
>> >> Hi Randi,
>> >>
>> >> I don't know if it's exactly the same as the DB2 version (probably not),
>> >> but SQL Server supports derived table expressions. Example (from BOL):
>> >>
>> >> USE pubs
>> >> GO
>> >> SELECT ST.stor_id, ST.stor_name
>> >> FROM stores AS ST,
>> >> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
>> >> FROM sales
>> >> GROUP BY stor_id
>> >> ) AS SA
>> >> WHERE ST.stor_id = SA.stor_id
>> >> AND SA.title_count = (SELECT COUNT(*) FROM titles)
>> >>
>> >> Best, Hugo
>> >> --
>> >>
>> >> (Remove _NO_ and _SPAM_ to get my e-mail address)
>>> >Hi Hugo, a common table expression, provided by the WITH
>> >clause, is defined in Standard SQL (beginning with SQL:1999)
>> >and is implemented in SQL Server 2005. Semantically, the WITH
>> >clause is similar to defining one or more views whose scope and
>> >extent is the enclosed query. Factoring out and naming these
>> >common subexpressions in a query is meant to aid readability,
>> >conciseness, maintainability, and even efficiency. There are cases
>> >when a derived table is a perfectly good alternative, however,
>> >when that derived table is used multiple times in the query a
>> >common table expression becomes handy.
>>> >Taking the BOL example from above, imagine you wanted to
>> >rank stores in decreasing order by number of distinct titles. Using
>> >WITH, one could write (admittedly, in this case a view is a reasonable
>> >choice too):
>>> >WITH DistinctTitles (stor_id, title_count) AS
>> > (SELECT stor_id, COUNT(DISTINCT title_id)
>> > FROM sales
>> > GROUP BY stor_id)
>> >SELECT T1.stor_id, T1.title_count,
>> > COUNT(DISTINCT T2.title_count) AS stor_rank
>> >FROM DistinctTitles AS T1
>> > INNER JOIN
>> > DistinctTitles AS T2
>> > ON T2.title_count >= T1.title_count
>> >GROUP BY T1.stor_id, T1.title_count;
>>> >It's also through the WITH clause that we can define recursive queries. This
>> >is where WITH truly shines.
>>
>> Just curious - can the scope of a WITH be more than one query? An entire
>> stored procedure, for instance?
>No, a WITH clause encloses a single query expression and can be used
>anywhere a query is used, e.g., in defining a view.

Darn - I thought this would finally be a tool for removing SQL code
duplication within stored procedures. Does SQL Server 2005 offer some other
new feature to do this?|||"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:5kr4t0l92t2pcit2rbgpa7c9naiutb7853@.4ax.com...
> On Wed, 29 Dec 2004 08:40:27 GMT, "John Gilson" <jag@.acm.org> wrote:
> >"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
> >news:h8q4t09uig7tn0lcro8ocjf6od0f6om854@.4ax.com...
> >> On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <jag@.acm.org> wrote:
> >>
> >> >"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> >> >news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
> >> >> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
> >> >>
> >> >> >Using SQL against a DB2 table the 'with' key word is used to
> >> >> >dynamically create a temporary table with an SQL statement that is
> >> >> >retained for the duration of that SQL statement.
> >> >> >What is the equivalent to the SQL 'with' using TSQL? If there is not
> >> >> >one, what is the TSQL solution to creating a temporary table that is
> >> >> >associated with an SQL statement? Examples would be appreciated.
> >> >> >Thank you!!
> >> >>
> >> >> Hi Randi,
> >> >>
> >> >> I don't know if it's exactly the same as the DB2 version (probably not),
> >> >> but SQL Server supports derived table expressions. Example (from BOL):
> >> >>
> >> >> USE pubs
> >> >> GO
> >> >> SELECT ST.stor_id, ST.stor_name
> >> >> FROM stores AS ST,
> >> >> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
> >> >> FROM sales
> >> >> GROUP BY stor_id
> >> >> ) AS SA
> >> >> WHERE ST.stor_id = SA.stor_id
> >> >> AND SA.title_count = (SELECT COUNT(*) FROM titles)
> >> >>
> >> >> Best, Hugo
> >> >> --
> >> >>
> >> >> (Remove _NO_ and _SPAM_ to get my e-mail address)
> >> >> >Hi Hugo, a common table expression, provided by the WITH
> >> >clause, is defined in Standard SQL (beginning with SQL:1999)
> >> >and is implemented in SQL Server 2005. Semantically, the WITH
> >> >clause is similar to defining one or more views whose scope and
> >> >extent is the enclosed query. Factoring out and naming these
> >> >common subexpressions in a query is meant to aid readability,
> >> >conciseness, maintainability, and even efficiency. There are cases
> >> >when a derived table is a perfectly good alternative, however,
> >> >when that derived table is used multiple times in the query a
> >> >common table expression becomes handy.
> >> >> >Taking the BOL example from above, imagine you wanted to
> >> >rank stores in decreasing order by number of distinct titles. Using
> >> >WITH, one could write (admittedly, in this case a view is a reasonable
> >> >choice too):
> >> >> >WITH DistinctTitles (stor_id, title_count) AS
> >> > (SELECT stor_id, COUNT(DISTINCT title_id)
> >> > FROM sales
> >> > GROUP BY stor_id)
> >> >SELECT T1.stor_id, T1.title_count,
> >> > COUNT(DISTINCT T2.title_count) AS stor_rank
> >> >FROM DistinctTitles AS T1
> >> > INNER JOIN
> >> > DistinctTitles AS T2
> >> > ON T2.title_count >= T1.title_count
> >> >GROUP BY T1.stor_id, T1.title_count;
> >> >> >It's also through the WITH clause that we can define recursive queries. This
> >> >is where WITH truly shines.
> >>
> >> Just curious - can the scope of a WITH be more than one query? An entire
> >> stored procedure, for instance?
> >No, a WITH clause encloses a single query expression and can be used
> >anywhere a query is used, e.g., in defining a view.
> Darn - I thought this would finally be a tool for removing SQL code
> duplication within stored procedures. Does SQL Server 2005 offer some other
> new feature to do this?

The idea behind the common table expression in a WITH clause is that
it doesn't act like a macro but is instead evaluated to a virtual table
that is used in each place where it's referenced in the enclosed query.
So in a stored procedure one might use a temp table or table variable
to store an intermediate result in lieu of such an animal. Nothing exciting
I'm afraid.

--
JAG|||John Gilson (jag@.acm.org) writes:
> The idea behind the common table expression in a WITH clause is that it
> doesn't act like a macro but is instead evaluated to a virtual table
> that is used in each place where it's referenced in the enclosed query.
> So in a stored procedure one might use a temp table or table variable
> to store an intermediate result in lieu of such an animal. Nothing
> exciting I'm afraid.

Nah, the current implementation appears to be quite macro-like, at least
for non-recursive queries.

When I look at the query plan for the query below, the CTE is computed
many times. For this query a temp table or a table variable would be
a much better alternative.

CREATE TABLE prodreport (id int NOT NULL,
product1 int NOT NULL,
product2 int NULL,
product3 int NULL,
CONSTRAINT pk_report PRIMARY KEY(id))
go
INSERT prodreport (id, product1)
SELECT PurchaseOrderID, MIN(ProductID)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail
GROUP BY PurchaseOrderID
go
-- This is the query of the show.
WITH temp (id, productid, rowno) AS
(SELECT PurchaseOrderID, ProductID,
rowno = (SELECT COUNT(*)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail p2
WHERE p1.PurchaseOrderID = p2.PurchaseOrderID
AND p1.ProductID >= p2.ProductID)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail p1)
UPDATE prodreport
SET product1 = t1.productid,
product2 = t2.productid,
product3 = t3.productid
FROM prodreport r
JOIN temp t1 ON t1.id = r.id
AND t1.rowno = 1
LEFT JOIN temp t2 ON t2.id = r.id
AND t2.rowno = 2
LEFT JOIN temp t3 ON t3.id = r.id
AND t3.rowno = 3

SELECT * FROM prodreport
go
DROP TABLE prodreport
go

I should that Umachandar Jaychandran, a former SQL Server MVP, rewrote
the query in this way:

WITH top_3_prods(id, productid, rowno) AS
(
SELECT PurchaseOrderID, ProductID,
ROW_NUMBER() OVER(PARTITION BY PurchaseOrderId
ORDER BY ProductID)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail p1
) ,
pvt_top_3_prods (id, product1, product2, product3) AS
(
SELECT id, [1], [2], [3]
FROM top_3_prods
PIVOT (min(ProductId) for rowno in ( [1], [2], [3] )) as pv
)
UPDATE prodreport
SET product1 = t1.product1,
product2 = t1.product2,
product3 = t1.product3
FROM prodreport r
JOIN pvt_top_3_prods t1 ON t1.id = r.id

There's a whole fireworks of new T-SQL features in that one!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp