Return Detail & Summary Tables From Stored Procedure 2

[Auguy]

I think this should be simple. I have a stored procedure that returns a dataset with a table of invoices that have not been printed. I would like it to also return a second table that contains the customer ID and a count of the invoices in the first table. I have other procedures that return two tables in a dataset, but none that have this kind of relationship. I can do this in the application, but would like to have SQL do it. The database is in SQL 2000

Auguy
Sylvania/Toledo Ohio

 

[markros]

Stored procedure can return as many select statements (datasets) as needed. The problem is to utilize them correctly in the client application. In ADO.NET you can get them either with DataTables in DataAdapter or through next result using DataReader.

PluralSight Learning Library

 

[Auguy]

Thanks Markros. I can and have returned multiple tables from one procedure. My problem is I want to summarize the records from the first table into a second table in the same procedure. I guess I don't know how to reference the first table in the second select.

Auguy
Sylvania/Toledo Ohio

 

[markros]

I see. In this case you need to first select into a temp table, e.g.

select DetailFields into #Result FROM ...

select * from #Result -- Details info

select SUM(...) from #Result -- Summary Info



PluralSight Learning Library

 

[Auguy]

Thanks again Markros.

Auguy
Sylvania/Toledo Ohio

 

[Auguy]

If anyone else is interested, here is a brief discussion of temporary tables and table varibles for SQL 2000.

http://www.sqlteam.com/article/temporary-tables

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

I actually disagree with the "Which to use" section.

The author says, if there are more than 100 rows, then you should use a temp table. I strongly disagree with this. If you have a WIDE temp table with lots of columns, this may be true, but if you have a narrow temp table, it's not. Think of it this way, SQL Server stores data in 8K pages. An int is 4 bytes. If you wanted to store a list of 1000 Integers, it would take 4,000 bytes, which easily fits in a single SQL Server data page. The best advise I can give regarding whether to use a temp table or table variable is... try it both ways and use which ever is faster.

The author also says: If you need to create indexes on it then you must use a temporary table. This is simply not true. You can create a primary key in a table variable which is implemented through an index. Sure... you can only have one primary key so you can only have one index, but you would be surprised at how often one index on a table variable is enough.

DECLARE @TibetanYaks TABLE (
YakID int [!]Primary Key[/!],
YakName char(30) )

Of course, this requires that the YakId column be unique, so you cannot always use this technique. However, even if it is not unique, you could still force the primary key to be unique by adding an identity column, like this:

DECLARE @TibetanYaks TABLE (
YakID int ,
UniqueId Int Identity(1,1),
YakName char(30) 
[!]Primary Key (YakID, UniqueId)[/!]
)

If you ever find yourself in a position where you want to do this, make sure the UniqueID appears last in the list of columns for the primary key.

In my own code, I've created table variables is hundreds of thousands of rows with a primary key that outperforms temp tables.

Personally, when I need temporary storage, I almost always start with a table variable. If performance is poor and a primary key doesn't help, then I will try a temp table.

Of course, your mileage may vary because this is highly dependent on data.

To create a table variable with an index...



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Thanks George, great information.

Auguy
Sylvania/Toledo Ohio