Monday, March 12, 2012

How to get results from a storedprocedure

To all,
I looked at the MS-SQL pubs sample database and execute the examplestored procedure reptq2 and I got 17 results set back. Where can I findan example using Visual Studio DataGrid or any means to get all theseresults from this SP.
Thanks,

Frank
Try this link for a two part tutorial in C# that can return 100 rows. Hope this helps.
http://www.dotnetjunkies.com/Tutorial/EA868776-D71E-448A-BC23-B64B871F967F.dcik|||I just remembered that Stored proc in Pubs used COMPUTE which is a none relational aggregate function in SQL Server so you have to make the code in the link work with the stored proc and Pubs. Hope this helps.|||Here's the same data from that stored procedure, in a single result set... Avoid COMPUTE BY.

use pubs
go

select
t.type,
t.pub_id,
t.title_id,
ta.au_ord,
Name = substring (a.au_lname, 1,15),
t.ytd_sales,
avg_pub.salessum as avg_pub,
avg_pub_type.salessum as avg_pub_type
from titles t
join titleauthor ta on t.title_id = ta.title_id
join authors a on a.au_id = ta.au_id
join
(
select
t.pub_id,
avg(t.ytd_sales)
from titles t
join titleauthor ta on t.title_id = ta.title_id
where
t.pub_id is NOT NULL
group by
t.pub_id
) avg_pub (pub_id, salessum) on avg_pub.pub_id = t.pub_id
join
(
select
t.pub_id,
t.type,
avg(t.ytd_sales)
from titles t
join titleauthor ta on t.title_id = ta.title_id
where
t.pub_id is NOT NULL
group by
t.pub_id,
t.type
) avg_pub_type (pub_id, type, salessum) on avg_pub_type.pub_id = t.pub_id and avg_pub_type.type = t.type
where
t.pub_id is NOT NULL
order by
t.type,
t.pub_id

No comments:

Post a Comment