October 17, 2003 at 6:16 am
Hi there,
I've created 4 sp's that use a select statement to retreive a set of records based on criteria passed in. These sps are used in reports, so the select statement contains the various fields needed and the procedure is returned into a dataset in .net which then produces the report.
I wish to now call these 4 sps from another sp which would be run overnight automatically. This sp would count just the number of records selected by each of the sp's and store the results in a table.
Will I have to insert each of the 4 sps into a temp table of some sorts and then do some sort of count? If i have to insert them into a table, will the table have to match the Select statemtent fields in each of the 4 sps?
Any code examples would be welcome!
Thanks
Dave
October 17, 2003 at 6:56 am
Thats one way. Another would be to return an output parameter from each proc that had the count.
Andy
October 17, 2003 at 10:01 am
Thanks for the suggestion of using OUTPUT. How would I use this with a select statement like:
Select * from Employee inner join etc where etc
How would the OUTPUT contain the count for the above sql?
Thanks
Dave
October 17, 2003 at 10:16 am
Set the output parameter = @@ROWCOUNT after you select the result set. If you do multiple select statements, you would set output parameter=(output parameter)+@@ROWCOUNT.
Select * from Employee inner join etc where etc
SET @OP=@@ROWCOUNT
October 20, 2003 at 8:14 am
Thanks bdohmen for the response. Changed SPs to use output, which sorted it out (in the end!)
Cheers
Dave
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply