Nested Cursors

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

Nested Cursors

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

Nested Cursor

I think I am getting an endless loop here... anyone know how to fix it?

***********************

CREATE PROCEDURE TrigSendPreNewIMAlertP2
@.REID int

AS

Declare @.RRID int
Declare @.ITID int
Declare @.FS2 int
Declare @.FS1 int

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
select @.FS1 = @.@.Fetch_Status

EXEC TrigSendNewIMAlertP2 @.ITID

FETCH NEXT FROM crImpGrp
into
@.ITID

close crImpGrp
deallocate crImpGrp

while @.@.Fetch_Status = 0
select @.FS2 = @.@.Fetch_Status

FETCH NEXT FROM crReqRec
into
@.RRID

close crReqRec
deallocate crReqRec
GOi think i had replied to you earlier with a similar question about cursors ? coz i recollect you did the same thing at that time..

heres a good template for a cursor..you should be able to figur eit out pretty easily for a nested cursor too :

DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT .........
OPEN rs
fetch next from rs into .....
WHILE ( @.@.FETCH_STATUS = 0 )
begin
 --do your processing here...in your case declare the nested cursor here
FETCH NEXT FROM rs INTO ......
END
close rs
deallocate rs

hth

Nested cursor

Is there a better way to nest cursors in SQL Server (I'm using 2005). Due to the nature of the solution a set-based approach will not work. Essentially I have a list of People. Each Person has Multiple Properties, so at the moment I have something along the lines of ...

Declare PersonCursor
Open PersonCursor
Fetch Next Person Cursor
While FetchStatus <> -1
Declare PropertyCursor (for Select according to current values in PersonCursor)
Open PropertyCursor
Fetch Next PropertyCursor
While @.@.FetchStatus <> -1
Process Each Property
Fetch Next PropertyCursor
End
Close PropertyCursor
Deallocate PropertyCursor
End
Close PersonCursor
DeallocatePersonCursor

I hope it's clear. What I'm asking is (and I don't think it's possible) is that is there a method by which I can Declare the PropertyCursor once, but open it multiple times using different values? I haven't used SQL Server cursors much but have seen this done in other RDBMS's.

Even hints on another angle to approach from would be useful. As I say, the steps involved at the "Process Each Property" stage are not really suited to a set-based approach, as I have to maintain counters, and dynamically build SQL unique to each iteration dependent on the counter values.

Any thoughts much appreciated.

Greg.

If the problem is not suited to a set based approach, why use SQL Server? Surely this would be easier to perform at the client side? You could still have the "process each property" code in a stored procedure if you like, but have it called from the client side instead. Can you use a set based appraoch using a CASE statement? CASE statments allow you to apply quite complex logic using a set based approach as it can do something different for each row returned from the query based on the value of that particular row.

HTH

|||We're using SQL Server mainly because

a) it's a heavily DB intensive process, and

b) in this instance, there is no client - the process is a Start of Financial Year billing process

We're using SQL Server stored procs to generate the billing data, and then SSIS to extract it to be printed.

Greg.

|||

Perhaps if you post more information about your specific calculation we can tell you how to do it using a set-based approach.

Oftentimes, this is actually possible (or we, as implementers, want to make that statement true since cursoring even in one level is often not efficient compared to the equivalent set-based query).

Conor Cunningham

SQL Server Query Optimization Development Lead

|||

A solution is to call in your outer cursor loop a sp where you can handle the property cursor.

|||

You can have the cursors nested, without a stored procedure, but you need to declare/open/fetch/close/deallocate the inner cursor within the WHILE loop of the outer cursor.

Good luck, Dean

need to write complex query without cursor

Hi all,
I am trying to do the following and am getting stuck.Your help wil be higly
appreciated.
I am matching two tables.If i get a single matching row then get that single
row
but if i get >1 matching rows i should select only the one with the latest
date.
for example
Table1
--
id value
1 10
2 20
3 30
Table 2
--
id param updateDate
1 10 day1
1 20 day2
1 25 day3
2 20 day2
2 40 day 3
3 30 day 2
(day1 <day2<day3 etc)
So i should get results as
Result
--
1 25 day3
2 40 day3
3 30 day2
how can I do this without a cursor?
Thanks for your help.on SQL 2005,
select id, param, updateDate
from table2
where row_number() over(partition by id order by updateDate desc) = 1
*untested*|||Try this, if you don't have SQL Server 2005:
select Table2.id, Table2.param, Table2.updateDate
from Table2
join Table1 on Table1.id = Table2.id
where updateDate = (
select max(updateDate) from Table2 as T_copy
where T_copy = Table2.id
)
or
select id, param, updateDate
from Table2
where updateDate = (
select max(updateDate) from Table2 as T_copy
where T_copy = Table2.id
) and exists (
select * from Table1
where Table1.id = Table2.id
)
This can yield multiple rows per id if (id, updateDate) is not unique
and there are two or more updates for an id on most recent update date.
For SQL Server 2005, Alexander has the right idea, but windowed
functions cannot appear in the WHERE clause, and you'll have to do this:
select id, param, updateDate
from (
select
id, param, updateDate,
row_number() over (partition by id order by updateDate desc) as rn
from Table2
) as T
where rn = 1
Steve Kass
Drew University
tech77 wrote:

>Hi all,
>I am trying to do the following and am getting stuck.Your help wil be higly
>appreciated.
>I am matching two tables.If i get a single matching row then get that singl
e
>row
> but if i get >1 matching rows i should select only the one with the latest
>date.
>for example
>Table1
>--
>id value
>1 10
>2 20
>3 30
>Table 2
>--
>id param updateDate
>1 10 day1
>1 20 day2
>1 25 day3
>2 20 day2
>2 40 day 3
>3 30 day 2
>(day1 <day2<day3 etc)
>So i should get results as
>Result
>--
>1 25 day3
>2 40 day3
>3 30 day2
>how can I do this without a cursor?
>Thanks for your help.
>|||Here's one method, assuming that each id will only have one row per
day. If you have multiple rows per id per day, you have to have some
rule to determine which row you want returned.
Stu
DECLARE @.Table1 TABLE (id int, value int)
INSERT INTO @.TABLE1
SELECT 1, 10
UNION ALL
SELECT 2, 20
UNION ALL
SELECT 3, 30
DECLARE @.Table2 TABLE (id int, param int, UpdateDate smalldatetime)
INSERT INTO @.TABLE2
SELECT 1, 10, '20060301'
UNION ALL
SELECT 1, 20, '20060302'
UNION ALL
SELECT 1, 25, '20060303'
UNION ALL
SELECT 2, 20, '20060302'
UNION ALL
SELECT 2, 40, '20060303'
UNION ALL
SELECT 3, 30, '20060302'
SELECT t2.id, t2.param, t2.UpdateDate
FROM @.table1 t1 JOIN @.Table2 t2 ON t1.id=t2.id
JOIN (SELECT id, MAX(updateDate) AS upDateDate
FROM @.Table2 t2
GROUP BY id) t2_2 ON t2.ID = t2_2.ID
AND t2.UpDateDate = t2_2.UpdateDate