Identifying results sets when stored procedure called multiple times

[wxbuff]

I have a report based on our product names that consists of two parts.
Both insert data into a temporary table.

1. A single grouped set of results based on all products
2. Multiple tables based on individual product names.

I am getting data by calling the same stored procedure multiple
times... for the single set of data I use "product like '%'"

To get the data for individual products, I am using a cursor to parse
the product list.

It's working great except that I have no idea how to identify the
results short of including a column with the product name. While that
is fine, I'm wondering if there is something that is like a header or
title that I could insert prior to generating the data that would look
a little tighter.

Thanks in advance-
Danielle
wx****@aol.com

[Erland Sommarskog]

(wx****@aol.com) writes:

I have a report based on our product names that consists of two parts.
Both insert data into a temporary table.

1. A single grouped set of results based on all products
2. Multiple tables based on individual product names.

I am getting data by calling the same stored procedure multiple
times... for the single set of data I use "product like '%'"

To get the data for individual products, I am using a cursor to parse
the product list.

It's working great except that I have no idea how to identify the
results short of including a column with the product name. While that
is fine, I'm wondering if there is something that is like a header or
title that I could insert prior to generating the data that would look
a little tighter.

I'm not really sure what you mean. You could insert a result set before
the main result set which has the header data. But then you need to
cope with multiple result sets, and not all tools handles this easily.

But it sounds to me, as there are potential for performance improvements
in the report. Ideally, you should get all data in one call to avoid
roundtrips. And cursors is something to avoid if possible. Then again,
you say "parse" which worries me. Data stored in a database should not
have to be parsed, if it is stored properly.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[wxbuff]

Erland -

Thanks for the reply.

I work for a software company whose support organiztion is broken into
groups. Each group supports certain products and within each group
there are multiple support levels (the typical level 1, level 2
etc...). The management would like a report to follow some parameters
such as the number of new support incidents in a given period, the
number closed etc...

At the first level, the report returns results based on the level of
the support worker regardless of the products.

The next level of breakdown is by product. That is where I used my
phrase 'parsing' - I have a cursor that reads the products for which
incidents were opened during the specified period and a table is
created summarizing the work done for each product.

As I sit here, I realize that there are efficiencies to be gained
(maybe? - I am still a novice) by using 'group by' etc... but I was
specfically asked to create a report that showed discrete tables for
each product. Hence the cursor.

I appreciate what you are saying about performance but the concern
there is not as high as it might be elsewhere as this report will run
on a weekend against a server which is not supporting other heavily
utilized applications.

Hope this helps clarify.

Danielle

[Erland Sommarskog]

(wx****@aol.com) writes:

The next level of breakdown is by product. That is where I used my
phrase 'parsing' - I have a cursor that reads the products for which
incidents were opened during the specified period and a table is
created summarizing the work done for each product.

As I sit here, I realize that there are efficiencies to be gained
(maybe? - I am still a novice) by using 'group by' etc... but I was
specfically asked to create a report that showed discrete tables for
each product. Hence the cursor.

So that's tables in the report, not database tables?

Well, a good reporting tool should be able to produce several tables
from one result set.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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