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...
This is the newer version but still getting the same results. PLEASE HELP...
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOALTER PROCEDURE InsNewEmployeeImpTaskP2
@.REID int,
@.LOID int,
@.RetValintoutputAS
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
@.RRIDset @.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
BeginUpdate RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRIDFETCH NEXT FROM crReqRec
into
@.RRID
endclose crReqRec
deallocate crReqRecGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
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
No comments:
Post a Comment