What is wrong with this snippet of XQuery like this
declare @.city xml
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>
{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}
}</Regions>
') as cities)
select @.city
It works fine without the <Region>{$r}</Region> and the pair of
brackets after itThe problem is that you have too many {}... {} are used in XQuery to switch
from the lexical XML construction syntax into XQuery syntax...
so in your case you have:
XQUERY CONTEXT 0> for $r in distinct-values(//Region)
> return
THIS SWITCHES TO XML CONSTRUCTION CONTEXT 1> <Regions>
THIS INTO XQUERY CONTEXT 1> {
TO XML CONSTRUCTION CONTEXT 2> <Region>
XQUERY CONTEXT 1> {$r
BACK OUT INTO OUTER XML CONTEXT 2> }
CLOSES XML CONTEXT 2> </Region>
TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
So you write either
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>,
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
or
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region>{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169755098.049990.46950@.l53g2000cwa.googlegroups.com...
> What is wrong with this snippet of XQuery like this
> declare @.city xml
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>
> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
> ') as cities)
> select @.city
> It works fine without the <Region>{$r}</Region> and the pair of
> brackets after it
>|||hi Michael
Thank you for all of your help.
I have some more questions re xpath/xquery
1. What is the comma <Region>{$r}</Region>,
2. Continuing from the query I asked about before, if i want to
return a count, how would I do that? I am doing this, but it returns
0
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region> {
for $mt in distinct-values(//MatterType)
return <MatterType ><count>{count(//Region[.=$r]/MatterType)}</
count></MatterType>
}</Regions>
'))
On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> The problem is that you have too many {}... {} are used in XQuery to switc
h
> from the lexical XML construction syntax into XQuery syntax...
> so in your case you have:
> XQUERY CONTEXT 0> for $r in distinct-values(//Region)> returnTHIS SWITCHES
TO XML CONSTRUCTION CONTEXT 1> <Regions>
> THIS INTO XQUERY CONTEXT 1> {
> TO XML CONSTRUCTION CONTEXT 2> <Region>
> XQUERY CONTEXT 1> {$r
> BACK OUT INTO OUTER XML CONTEXT 2> }
> CLOSES XML CONTEXT 2> </Region>
> TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
>
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>,
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> or
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region>{
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> Best regards
> Michael
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169755098.0499
90.46950@.l53g2000cwa.googlegroups.com...
>
>
>|||and also
3. how would i create the xquery string to return a value as an
attribute rather than as an element as I've done before? (return
<MatterType>{$mt}</MatterType> )
Thanks!
On Jan 29, 11:51 am, "joyce" <joycesc...@.fastmail.fm> wrote:
> hi Michael
> Thank you for all of your help.
> I have some more questions re xpath/xquery
> 1. What is the comma <Region>{$r}</Region>,
> 2. Continuing from the query I asked about before, if i want to
> return a count, how would I do that? I am doing this, but it returns
> 0
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region> {
> for $mt in distinct-values(//MatterType)
> return <MatterType ><count>{count(//Region[.=$r]/MatterTyp
e)}</
> count></MatterType>
> }</Regions>
> '))
> On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
Showing posts with label snippet. Show all posts
Showing posts with label snippet. Show all posts
Monday, March 12, 2012
nested for loop with xquery
What is wrong with this snippet of XQuery like this
declare @.city xml
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>
{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}
}</Regions>
') as cities)
select @.city
It works fine without the <Region>{$r}</Region> and the pair of
brackets after it
The problem is that you have too many {}... {} are used in XQuery to switch
from the lexical XML construction syntax into XQuery syntax...
so in your case you have:
XQUERY CONTEXT 0> for $r in distinct-values(//Region)
> return
THIS SWITCHES TO XML CONSTRUCTION CONTEXT 1> <Regions>
THIS INTO XQUERY CONTEXT 1> {
TO XML CONSTRUCTION CONTEXT 2> <Region>
XQUERY CONTEXT 1> {$r
BACK OUT INTO OUTER XML CONTEXT 2> }
CLOSES XML CONTEXT 2> </Region>
TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
So you write either
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>,
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
or
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region>{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169755098.049990.46950@.l53g2000cwa.googlegro ups.com...
> What is wrong with this snippet of XQuery like this
> declare @.city xml
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>
> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
> ') as cities)
> select @.city
> It works fine without the <Region>{$r}</Region> and the pair of
> brackets after it
>
|||hi Michael
Thank you for all of your help.
I have some more questions re xpath/xquery
1. What is the comma <Region>{$r}</Region>,
2. Continuing from the query I asked about before, if i want to
return a count, how would I do that? I am doing this, but it returns
0
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region> {
for $mt in distinct-values(//MatterType)
return <MatterType ><count>{count(//Region[.=$r]/MatterType)}</
count></MatterType>
}</Regions>
'))
On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> The problem is that you have too many {}... {} are used in XQuery to switch
> from the lexical XML construction syntax into XQuery syntax...
> so in your case you have:
> XQUERY CONTEXT 0> for $r in distinct-values(//Region)> returnTHIS SWITCHES TO XML CONSTRUCTION CONTEXT 1> <Regions>
> THIS INTO XQUERY CONTEXT 1> {
> TO XML CONSTRUCTION CONTEXT 2> <Region>
> XQUERY CONTEXT 1> {$r
> BACK OUT INTO OUTER XML CONTEXT 2> }
> CLOSES XML CONTEXT 2> </Region>
> TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
>
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>,
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> or
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region>{
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> Best regards
> Michael
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169755098.049990.46950@.l53g2000cwa.go oglegroups.com...
>
|||and also
3. how would i create the xquery string to return a value as an
attribute rather than as an element as I've done before? (return
<MatterType>{$mt}</MatterType>)
Thanks!
On Jan 29, 11:51 am, "joyce" <joycesc...@.fastmail.fm> wrote:[vbcol=seagreen]
> hi Michael
> Thank you for all of your help.
> I have some more questions re xpath/xquery
> 1. What is the comma <Region>{$r}</Region>,
> 2. Continuing from the query I asked about before, if i want to
> return a count, how would I do that? I am doing this, but it returns
> 0
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region> {
> for $mt in distinct-values(//MatterType)
> return <MatterType ><count>{count(//Region[.=$r]/MatterType)}</
> count></MatterType>
> }</Regions>
> '))
> On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
declare @.city xml
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>
{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}
}</Regions>
') as cities)
select @.city
It works fine without the <Region>{$r}</Region> and the pair of
brackets after it
The problem is that you have too many {}... {} are used in XQuery to switch
from the lexical XML construction syntax into XQuery syntax...
so in your case you have:
XQUERY CONTEXT 0> for $r in distinct-values(//Region)
> return
THIS SWITCHES TO XML CONSTRUCTION CONTEXT 1> <Regions>
THIS INTO XQUERY CONTEXT 1> {
TO XML CONSTRUCTION CONTEXT 2> <Region>
XQUERY CONTEXT 1> {$r
BACK OUT INTO OUTER XML CONTEXT 2> }
CLOSES XML CONTEXT 2> </Region>
TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
So you write either
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>,
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
or
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region>{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169755098.049990.46950@.l53g2000cwa.googlegro ups.com...
> What is wrong with this snippet of XQuery like this
> declare @.city xml
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>
> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
> ') as cities)
> select @.city
> It works fine without the <Region>{$r}</Region> and the pair of
> brackets after it
>
|||hi Michael
Thank you for all of your help.
I have some more questions re xpath/xquery
1. What is the comma <Region>{$r}</Region>,
2. Continuing from the query I asked about before, if i want to
return a count, how would I do that? I am doing this, but it returns
0
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region> {
for $mt in distinct-values(//MatterType)
return <MatterType ><count>{count(//Region[.=$r]/MatterType)}</
count></MatterType>
}</Regions>
'))
On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> The problem is that you have too many {}... {} are used in XQuery to switch
> from the lexical XML construction syntax into XQuery syntax...
> so in your case you have:
> XQUERY CONTEXT 0> for $r in distinct-values(//Region)> returnTHIS SWITCHES TO XML CONSTRUCTION CONTEXT 1> <Regions>
> THIS INTO XQUERY CONTEXT 1> {
> TO XML CONSTRUCTION CONTEXT 2> <Region>
> XQUERY CONTEXT 1> {$r
> BACK OUT INTO OUTER XML CONTEXT 2> }
> CLOSES XML CONTEXT 2> </Region>
> TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
>
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>,
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> or
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region>{
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> Best regards
> Michael
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169755098.049990.46950@.l53g2000cwa.go oglegroups.com...
>
|||and also
3. how would i create the xquery string to return a value as an
attribute rather than as an element as I've done before? (return
<MatterType>{$mt}</MatterType>)
Thanks!
On Jan 29, 11:51 am, "joyce" <joycesc...@.fastmail.fm> wrote:[vbcol=seagreen]
> hi Michael
> Thank you for all of your help.
> I have some more questions re xpath/xquery
> 1. What is the comma <Region>{$r}</Region>,
> 2. Continuing from the query I asked about before, if i want to
> return a count, how would I do that? I am doing this, but it returns
> 0
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region> {
> for $mt in distinct-values(//MatterType)
> return <MatterType ><count>{count(//Region[.=$r]/MatterType)}</
> count></MatterType>
> }</Regions>
> '))
> On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
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
Subscribe to:
Posts (Atom)