What is the best way to nest cursors?
This code does not seem to be returning me all of the data.
Code Snippet
DECLARE element_Cursor CURSOR FOR
SELECT ElementTypeRecNo
FROM dbo.tblTemplateElementType
where TemplateRecno = @.TemplateRecNo
OPEN element_cursor
FETCH NEXT FROM Element_Cursor into @.ElementTypeRecno
--delete from tblElementCPO
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.Count = count (*)
from tblProjTypeSet
where ProjRecno = @.ProjRecNo
if @.Count > 0
begin
select @.ProjTypeRecno = ProjTypeRecno
from tblProjTypeSet
where ProjRecno = @.ProjRecNo
select @.Count = count (*)
FROM dbo.tblElementTypeDep
where TemplateRecno = @.TemplateRecNo
and ProjTypeRecno = @.ProjTypeRecNo
if @.Count > 0
begin
DECLARE ElementTypeDep_Cursor CURSOR FOR
SELECT ElementTypeDepRecNo, PreElementTypeRecNo,
PostElementTypeRecNo, ElapsedTimeDueDates, ElapsedTimePlanDates,
Description
FROM tblElementTypeDep
WHERE (TemplateRecNo = @.TemplateRecNo)
AND (ProjTypeRecNo = @.ProjTypeRecno)
AND (PreElementTypeRecNo = @.ElementTypeRecno)
OPEN ElementTypeDep_cursor
FETCH NEXT FROM ElementTypeDep_Cursor
into @.ElementTypeDepRecno, @.PreElementTypeRecNo,
@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,
@.Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.PreElementRecNo = ElementRecno
from tblElementCPO
where ProjRecNo = @.ProjRecNo
and IssueRecno = @.IssueRecNo
and ElementTypeRecno = @.PreElementTypeRecno
if @.PreElementRecno is not null
begin
select @.PostElementRecNo = ElementRecno
from tblElementCPO
where ProjRecNo = @.ProjRecNo
and IssueRecno = @.IssueRecNo
and ElementTypeRecno = @.PostElementTypeRecno
if @.PostElementRecno is not null
begin
select @.Count = count (*)
from tblElementDepCPO
where ElementTypeDepRecno = @.ElementTypeDepRecno
and PreElementRecNo = @.PreElementRecNo
and PostElementRecno = @.PostElementRecno
if @.Count = 0
begin
INSERT INTO tblElementDepCPO
(ElementTypeDepRecNo, PreElementRecNo,
PostElementRecNo, ElapsedTimeDueDates,
ElapsedTimePlanDates, Description,
ChangeDate, ChangePerson)
VALUES (@.ElementTypeDepRecno, @.PreElementRecNo,
@.PostElementRecno, @.ElapsedTimeDueDates,
@.ElapsedTimePlanDates, @.Description,
GETDATE(), CURRENT_USER)
end
select @.Count = count (*)
from tblElementAttemptCPO
where ElementRecNo = @.PostElementRecNo
if @.Count = 0
begin
select @.Count = count (*)
from tblElementAttemptCPO
where ElementRecNo = @.PostElementRecNo
if @.Count = 0
begin
select @.NextPlanDate = ProjectedCompletionDate,
@.NextDueDate = RequiredCompletionDate
from tblElementAttemptCPO
where ElementRecno = @.PreElementRecNo
end
else
begin
select @.NextPlanDate = @.StartDate
select @.NextDueDate = @.StartDate
end
select @.NextPlanDate =
dbo.fncAddBusinessDays (@.NextPlanDate, @.ElapsedTimePlanDates)
select @.NextDueDate =
dbo.fncAddBusinessDays (@.NextDueDate, @.ElapsedTimePlanDates)
insert into tblElementAttemptCPO (ElementRecno,
ProjectedCompletionDate, RequiredCompletionDate,
ProjectedStartDate, RequiredStartDate,
ActualStartDate, ActualCompletionDate, AttemptNum,
IsCompleted, IsStarted, ResponsibleRoleTypeRecno,
ChangeDate, ChangePerson)
values (@.PostElementRecno,
@.NextPlanDate, @.NextDueDate,
'1/11/1900', '1/11/1900',
'1/11/1900', '1/11/1900', 0,
0, 0, 0,
GETDATE(), CURRENT_USER)
end
end
end
FETCH NEXT
FROM ElementTypeDep_Cursor
into @.ElementTypeDepRecno, @.PreElementTypeRecNo,
@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,
@.Description
END
CLOSE elementTypeDep_Cursor
DEALLOCATE elementTypeDep_Cursor
end
FETCH NEXT FROM element_Cursor into @.ElementTypeRecno
END
CLOSE element_Cursor
DEALLOCATE element_Cursor
end
There is a single insert statement hidden within the cursors. Since there is no select statement, there wouldn't be any data returned. Exactly what are you trying to return?
Also, I suggest you post DDL+sample data (i.e. insert statement)+expected output here. We might be able to help draft a non-cursor version.
|||As oj implied, cursors are extremely taxing to a SQL Server and generally should be avoided if possible. Is some cases, it's not possible. But if you'll post the info that oj requested, perhaps this is a case where they can be avoided.Joe
