Looping in SQL Stored Procedure - Help 1

[UrbaneRove]

Hey all, I don't know if this can be done but any help is greatly appreciated.

I would like to utilize two separate Stored Procedures, one as a master which will bring in one bit of data that needs to be returned and a slave that will bring in the rest of the data based on two variables sent off the master. I need to tier the information to be returned from one call to the master stored procedure.

The question is how do you loop through a recordset in a stored procedure that has of course multiple records. Then using an Execute call a second Stored Procedure that also will return multiple records for each of the multiple record in the first SP. Then return the header values from the first Stored Procedure and all the associated records from the second



Ex. 1st 2nd
Name start end ID value

Bob 123 211 125 $565
154 $443
205 $665
Joe 211 344 224 $343
254 $488
266 $534
324 $232
Matt 344 515 348 $343
354 $543
362 $334


The first SP brings back the people that worked on one day with their start and ending invoice numbers. The second SP brings back the invoices that are for that employee. There is no link between the two except for the beginning and ending invoice numbers. These number do not overlap in the application.

1-looping with SQL in a Stored Procedure
2-returning information in one recordset off two sql statements in one stored procedure.

Regard

Urbane Rove.

 

[skuhlman]

Here's just a basic example of looping through a cursor. You'd actually want to make some changes to this to make it useful, but it will give you the general idea:

DECLARE MyCursor CURSOR FOR
SELECT DISTINCT xname, startnum, endnum
FROM MyTableA
OPEN MyCursor

DECLARE @xname CHAR(50)
DECLARE @startnum INTEGER
DECLARE @endnum INTEGER

CREATE TABLE #TempDetail (name CHAR(50), startnum INTEGER, endnum INTEGER)

FETCH NEXT FROM MyCursor
INTO @xname, @startnum, @endnum

WHILE @@FETCH_STATUS = 0
BEGIN
/* other code can go inside this loop */
exec sp_SecondStoredProcedure @startnum, @endnum
FETCH NEXT FROM MyCursor
INTO @xname, @startnum, @endnum
END
CLOSE MyCursor
DEALLOCATE MyCursor
DROP TABLE #TempDetail



Now, that being said, I'm not sure that it's necessary to do this. It may depend upon wheather or not you're trying to get header information onto the same rows as the detail or on seperate rows. Would your records look like

A
1
2
B
1
2
3
C
1

or like

A1
A2
B1
B2
B3
C1

when you are done...

If it's like the first set, you may need to do the looping. If it's like the second set, you may be able to get away with being creative on a join statement. Here's a join that might work:

SELECT a.xname,
a.startnum,
a.endnum,
b.idnum,
sum(b.nvalue) as totvalue
from a join b on a.startnum<=b.idnum and a.endnum>=b.idnum
where a.xname = 'Sam'
and b.activeinvoice = 'YES'
group by a.xname, a.startnum, a.endnum, b.idnum
order by a.xname

 

[UrbaneRove]

Thanks it was a great help.

UR