Monday, March 26, 2012

Fetch within a fetch

Is it possible to have fetch within a fetch? I am getting this error message "A cursor with the name 'crImgGrp' does not exist." So i separate the process into two stored procedures?

CREATE PROCEDURE TrigSendPreNewIMAlertP2
@.REID int

AS

Declare @.RRID int
Declare @.ITID int

Declare @.intIMEmail varchar(300)

Declare crReqRec cursor for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'IA' and APID is not null
open crReqRec
fetch next from crReqRec
into
@.RRID

Declare crImpGrp cursor for
select ITID from RequestRecords where RRID = @.RRID
open crImpGrp
fetch next from crImgGrp
into
@.ITID
while @.@.fetch_status = 0

EXEC TrigSendNewIMAlertP2 @.ITID

FETCH NEXT FROM crImpGrp
into
@.ITID

close crImpGrp
deallocate crImpGrp

while @.@.fetch_status = 0

FETCH NEXT FROM crReqRec
into
@.RRID

close crReqRec
deallocate crReqRec
GO... I'd re-think a different solution than what you want to do.

No comments:

Post a Comment