January 22, 2010 at 8:38 am
I have a stored procedure with a cursor.
The select part shows 7 lines.
So the accountcursor must do the update 7 time.
But it starts and doesn't stop, looks like a loop.
Who can help me?
Henk-Jan
BEGIN
declare @userinteger_01 as [varchar] (30)
declare @Number as [varchar] (30)
declare @ItemCode as [varchar] (30)
declare @UserField_03 as [varchar] (255)
declare @Description as [varchar] (60)
declare @UserField_02 as [varchar] (255)
declare @UserField_01 as [varchar] (255)
declare @Date as datetime
declare @status as [varchar] (16)
declare @cmp_wwn as uniqueidentifier
declare accountcursor cursor for
SELECT CAST(b.UserNumber_01 AS int) AS userinteger_01, b.Number, b.ItemCode, b.UserField_03, b.Description, b.UserField_02, b.UserField_01,
CAST(b.[Date] AS datetime) AS [date], b.Status, c.cmp_wwn
FROM dbo.ItemNumbers a with (nolock) INNER JOIN
(SELECT CAST(UserNumber_01 AS int) as UserNumber_01, number, itemcode, userfield_03, description, userfield_02,debnumber, userfield_01,status,date
FROM dbo.ItemNumbers_metcal with (nolock)
WHERE
(isnumeric(dbo.ItemNumbers_metcal.UserNumber_01) = 1)
AND (CAST(dbo.ItemNumbers_metcal.UserNumber_01 AS float) < 2147483647)
AND dbo.ItemNumbers_metcal.status in ('A', 'I')
) as b
ON a.UserInteger_01 = b.UserNumber_01
LEFT OUTER JOIN
dbo.cicmpy c
ON ltrim(rtrim(b.DebNumber)) = ltrim(rtrim(c.debnr))
where userinteger_01 <> 0
----open de cursor
open accountcursor
----get first row op
fetch next from accountcursor
into @userinteger_01, @Number, @ItemCode, @UserField_03, @Description, @UserField_02, @UserField_01, @Date, @status, @cmp_wwn
----till end
While @@FETCH_STATUS =0
BEGIN
----stored procedure
update itemnumbers set userinteger_01 = @userinteger_01, Number = @Number, ItemCode = @ItemCode, UserField_03 = @UserField_03,Description = @Description+ ' '+ @UserField_03 ,
UserField_02 = @UserField_02, Associate = @cmp_wwn, UserField_01 = @UserField_01, Userdate_04 = @date, Userdate_02 = getdate ()
where userinteger_01 = @userinteger_01
----volgende regel
fetch next from accountcursor
into @userinteger_01, @Number, @ItemCode, @UserField_03, @Description, @UserField_02, @UserField_01, @Date, @status, @cmp_wwn
END
BEGIN
close accountcursor
deallocate accountcursor
END
END
January 22, 2010 at 10:21 am
What is the specific reason to use a cursor for this update?
Why not doing it all in one path?
January 22, 2010 at 12:35 pm
Along with what Lutz said, the update does not make sense. Look at this part of your update statement..
update itemnumbers
set userinteger_01 = @userinteger_01
...
where userinteger_01 = @userinteger_01
Why are you updating the column userinteger_01 with the same value again? Isnt userinteger_01 your PK?
This whole cursor can be written as a one single set based query.
It would be more like
Update itmNum
set Number = drv.Number , ItemCode = drv.ItemCode ,
.....
from itemnumbers as itmNum
JOIN (
SELECT CAST(b.UserNumber_01 AS int) AS userinteger_01, b.Number, b.ItemCode, b.UserField_03, b.Description, b.UserField_02, b.UserField_01,
CAST(b.[Date] AS datetime) AS [date], b.Status, c.cmp_wwn
FROM dbo.ItemNumbers a with (nolock) INNER JOIN
(SELECT CAST(UserNumber_01 AS int) as UserNumber_01, number, itemcode, userfield_03, description, userfield_02,debnumber, userfield_01,status,date
FROM dbo.ItemNumbers_metcal with (nolock)
WHERE
(isnumeric(dbo.ItemNumbers_metcal.UserNumber_01) = 1)
AND (CAST(dbo.ItemNumbers_metcal.UserNumber_01 AS float) < 2147483647)
AND dbo.ItemNumbers_metcal.status in ('A', 'I')
) as b
ON a.UserInteger_01 = b.UserNumber_01
LEFT OUTER JOIN
dbo.cicmpy c
ON ltrim(rtrim(b.DebNumber)) = ltrim(rtrim(c.debnr))
where userinteger_01 <> 0
) as drv
on itmNum.userinteger_01 = drv.userinteger_01
This is just a pseudo code. SO you will have to do a bit of work on this. Using NOLOCK hint is not such a good idea.
-Roy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply