Let's say i have 5 unique RRID's, column APID and ITID
RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 700
5 15 700
If I run the stored procedure below, I get the results above however, I want my result to be
RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 701
5 15 702
I want my cursor to loop at the same APID then assign one ITID then move to the next APID and so on...
Any help is highly appreciated...
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOALTER PROCEDURE InsNewEmployeeImpTaskP2
@.REID int,
@.LOID int,
@.RetValintoutput
AS
Declare @.RRID int
Declare @.APID int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'AC' and APID is not null
open crReqRec
fetch next from crReqRec
into
@.RRID
set @.APID = (select APID from RequestRecords where REID = @.REID and RRID = @.RRID)
set @.intIMID = (SELECT ImplementationGroup.IMID
FROM ImplementationGroup_Location INNER JOIN
ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN
Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @.APID and ImplementationGroup_Location.LOID = @.LOID )
insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@.intIMID,
'2',
GetDate()
)
SET @.RetVal = @.@.Identity
while @.@.fetch_status = 0
Begin
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID
FETCH NEXT FROM crReqRec
into
@.RRID
end
close crReqRec
deallocate crReqRec
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
This is the newer version but still getting the same results. PLEASE HELP...
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE InsNewEmployeeImpTaskP2
@.REID int,
@.LOID int,
@.RetValintoutput
AS
Declare @.RRID int
Declare @.APID int
Declare @.APID2 int
Declare @.FS1 int
Declare @.FS2 int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)
Declare crReqRec cursor local for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'AC' and APID is not null
open crReqRec
fetch next from crReqRec into @.RRID
set @.FS1 = @.@.fetch_status
Declare crAPID cursor local for
select APID from RequestRecords where REID = @.REID and RRID = @.RRID
open crAPID
fetch next from crAPID into @.APID2
set @.FS2 = @.@.fetch_status
set @.APID2 = (select APID from RequestRecords where APID = @.APID)
set @.intIMID = (SELECT ImplementationGroup.IMID FROM ImplementationGroup_Location INNER JOIN
ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN
Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @.APID2 and ImplementationGroup_Location.LOID = @.LOID )
insert into ImplementationTasks
(IMID, ITStatus,ITStatusDate) VALUES (@.intIMID,'2',GetDate())
SET @.RetVal = @.@.Identity
while @.FS2 = 0
while @.FS1 = 0
Begin
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID
fetch next from crAPID into @.APID2
FETCH NEXT FROM crReqRec into @.RRID
end
close crReqRec
deallocate crReqRec
close crAPID
deallocate crAPID
--EXEC TrigRetReqRecIDP2 @.REID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql
