creating a trace definition file (TDF) via code

Is there any way (either TSQL/SMO) to create a new TDF file, I'm using the traceserver InitializeAsReader sub, so need to pass it a reference to a TDF file, and I'd like to control what it traces via the TDF rather than parsing the TextData of the output.

Cathal

There is a Trace API that ships with SMO (see the Microsoft.SqlServer.Management.Trace namespace).

See the TraceServer.InitializeAsReader() method. You can specify a predefine trace template as the second parameter and then start the trace with this template.

|||

Thanks Michiel, but I'm actually using that method already, my question is on the creation of the TDF itself.

Ideally I'd like a t-sql/smo script to generate a TDF template, so I could dynamically determine what events to track and also filter for particular users/databases e.g. if I was only interested in tracking northwind events, I'd create a template based on TSQL_SPs, but that also used a DatabaseName column filter set to like '%Northwind%' . Obviously i can do this manually, but I would like to automate this step for an application I'm writing. I suspect it's not possible to serialize a trace definition to a TDF file. I can use TSQL to create a trace with the correct filters I require, but as I can't pass a running trace to InitializeAsReader, I can't use it. At present I've assumed I'll have to filter the eventData via code, but I thought I'd ask just in case.

Cathal

|||This is not possible, AFAIK. I'll make sure this gets logged as a feature request.

Creating a cursor with a dynamic database name.

You will either know this or you won't. I want to do thisthe following two lines of TSQL in one dynamically but none of the Declare Cursor statements work (when I try to pass in the Database name using a parameter). How do i dynamicically create a cursor to a table using a dynamic database/catalog name?

DECLARE curTest1 CURSOR SELECT * FROM testDB1.dbo.MyTable
DECLARE curTest2 CURSOR SELECT * FROM testDB2.dbo.MyTable

I've tried the following

DECLARE @.CatalogName NVARCHAR(5)
DECLARE @.sqlStr NVARCHAR (4000)

SET @.CatalogName = 'TestDB'
SET @.sqlStr = 'SELECT * FROM ' + @.CatalogName + 'dbo.Mytable;'

DECLARE curTest CURSOR FOR SELECT * FROM @.CatalogName.dbo.MyTable -- Which obviously should not and does not work.
DECLARE curTest CURSOR FOR @.sqlSTR -- Which I thought would work but also does not work.

CLOSE curTest
DEALLOCATE curTest

My environment is SQL Server 2000 environment SP3the answer you are looking for is sp_executesql....

you can't use a parameter value in the way you are trying...

you would need to dynamicly create and execute your sql statement using sp_executesql.

there have been a few posts in the last few days that explain this to the nth degree.|||You can't declare with dynamic sql...

DECLARE @.declare varchar(2000)

SET @.declare = 'DECLARE @.x int'

sp_executesql(@.declare)

And why do you want to use a cursor?

Think of dynamic sql being "outside" the scope of the current thread...|||Actually you're both wrong. I figured it out.

Turns out you need to use the EXEC command to execute the string.|||Yes I am...and I wish I wasn't

Why would you want to do this?

You going to build the Fetches dynamically?

How about the Declarations of the variables...That I don't think you can do indynamic sql

but this (to my UTTER amazement)..wrks:

USE Northwind
GO

DECLARE @.cmd varchar(8000), @.ShippedDate datetime
SELECT @.cmd = 'DECLARE myCursor CURSOR FOR SELECT ShippedDate FROM Orders'
EXEC(@.Cmd)
OPEN myCursor
FETCH NEXT FROM myCursor INTO @.ShippedDate
SELECT @.ShippedDate
CLOSE myCursor
DEALLOCATE myCursor

Good luck...|||The short answer is consolidated reporting on Accounting systems.

Most modern accounting systems allow for multiple companies to be managed from one server. To accomodate this, a seperate database is created for each company but fortunately the structure of the tables does not change between companyies . As a result, to report consolidated figures for the entire organization you want to have catalog names passed in dynamically especially if your oganization contains many companies.|||Can you post the sproc?

I'd like to see if there's a non cursor way...|||Here is one of them.

CREATE PROCEDURE sp_Sales_Summary_Update_02_03

@.CatalogName NVARCHAR(5),
@.ItemNumber NVARCHAR(31),
@.Warehouse NVARCHAR(11),
@.PurchaseTableName NVARCHAR(8),
@.PurchaseLineTableName NVARCHAR(8)
AS

DECLARE @.FromTheYear INT
DECLARE @.FromTheWeek INT
DECLARE @.ToTheYear INT
DECLARE @.ToTheWeek INT
DECLARE @.QuantityOrdered NUMERIC(19,5)
DECLARE @.strCursorString NVARCHAR(4000)

SET @.strCursorString = ''
SET @.strCursorString = @.strCursorString + 'DECLARE curQuantityOrdered CURSOR FORWARD_ONLY FOR '
SET @.strCursorString = @.strCursorString + 'SELECT '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE) AS FromTheYear, '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE) AS FromTheWeek, '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE) AS ToTheYear, '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE) AS ToTheWeek, '
SET @.strCursorString = @.strCursorString + ' ''' + @.CatalogName + ''' AS CompanyID, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR AS ItemNumber, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE AS Warehouse, '

SET @.strCursorString = @.strCursorString + ' SUM(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.QTYORDER) AS QuantityOrdered '
SET @.strCursorString = @.strCursorString + 'FROM ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + ' LEFT OUTER JOIN ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + ' '
SET @.strCursorString = @.strCursorString + ' ON ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PONUMBER = ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.PONUMBER '
SET @.strCursorString = @.strCursorString + 'WHERE '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.POSTATUS <> 6) AND '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.QTYORDER <> 0) '
SET @.strCursorString = @.strCursorString + 'GROUP BY '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE '
SET @.strCursorString = @.strCursorString + 'HAVING '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR = ''' + @.ItemNumber + ''') AND '
SET @.strCursorString = @.strCursorString + ' (''' + @.CatalogName + ''' = ''' + @.CatalogName + ''') AND '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE = ''' + @.Warehouse + ''') '
SET @.strCursorString = @.strCursorString + 'ORDER BY '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE '
PRINT @.strCursorString
EXECUTE(@.strCursorString)

OPEN curQuantityOrdered

FETCH NEXT FROM curQuantityOrdered INTO @.FromTheYear, @.FromTheWeek, @.ToTheYear, @.ToTheWeek, @.CatalogName, @.ItemNumber, @.Warehouse, @.QuantityOrdered
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE tblSalesSummary
SET QuantityOrdered = @.QuantityOrdered
FROM
tblSalesSummary
WHERE
(CompanyID = @.CatalogName) AND
(ItemNumber = @.ItemNumber) AND
(Warehouse = @.Warehouse) AND
CASE
WHEN TheWeek < 10 THEN
CAST(TheYear AS nvarchar(4)) + '0'+ CAST(TheWeek AS nvarchar(2))
ELSE
CAST(TheYear AS nvarchar(4)) + CAST(TheWeek AS nvarchar(2))
END
BETWEEN
CASE
WHEN @.FromTheWeek < 10 THEN
CAST(@.FromTheYear AS nvarchar(4)) + '0'+ CAST(@.FromTheWeek AS nvarchar(2))
ELSE
CAST(@.FromTheYear AS nvarchar(4)) + CAST(@.FromTheWeek AS nvarchar(2))
END
AND
CASE
WHEN @.ToTheWeek < 10 THEN
CAST(@.ToTheYear AS nvarchar(4)) + '0'+ CAST(@.ToTheWeek AS nvarchar(2))
ELSE
CAST(@.ToTheYear AS nvarchar(4)) + CAST(@.ToTheWeek AS nvarchar(2))
END

FETCH NEXT FROM curQuantityOrdered INTO @.FromTheYear, @.FromTheWeek, @.ToTheYear, @.ToTheWeek, @.CatalogName, @.ItemNumber, @.Warehouse, @.QuantityOrdered
END
CLOSE curQuantityOrdered
DEALLOCATE curQuantityOrdered
GO|||It Looks like alot but is really 2 steps. The first BLOB of TSQL creates the cursor. The second BLOB of TSQL updates the destination table.|||WOW! Dude...how long does it take to run?|||This particular sp executes in a fraction of a second. However it is run multiple times and depending on the volume of data required for processing can add up to hours (inconjunction with the other sp's I have running).

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