May 12, 2009 at 6:04 am
Hi,
I need to update a table records (suppose fill each field with a calculated value) but when I run following statement, it creates unlimited loop! could anyone let me know what the problem is?
DECLARE Cur CURSOR FOR select OrgStrID from HRM.tb_OrgStructure where ParentID=@OrgStrID
OPEN Cur
FETCH NEXT FROM Cur
INTO @OrgStrID_Priority
WHILE @@FETCH_STATUS = 0
BEGIN --1
select @OrgStrID_Priority
--select (@max+1) from HRM.tb_OrgStructure where OrgStrID=@OrgStrID_Priority
update HRM.tb_OrgStructure set OrgStrPriority=(@max+1) where OrgStrID=@OrgStrID_Priority
--set @max-2=@max+1
FETCH NEXT FROM Cur
INTO @OrgStrID_Priority
END --1
CLOSE Cur
DEALLOCATE Cur
May 12, 2009 at 6:54 am
I don't see any reason for an infinite loop here, but I could be missing something.
I also think you can probably do this without a cursor which will be much faster. Something like this might work:
WITH cteMax AS
(
SELECT
OrgStrId,
ROW_NUMBER() OVER(PARTITION BY Parent_ID ORDER BY OrgStrID) AS ORgStrPriority
FROM
HRM.tb_OrgStructure
WHERE
ParentID - @OrgStrID
)
UPDATE htm.tb_OrgStructure
SET OrgStrPriority = CM.OrgStrPriority
FROM
hrm.tb_OrgStructure T JOIN
cteMax CM ON T.OrgStrID = CM.OrgStrID
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 12, 2009 at 7:03 am
May 12, 2009 at 10:41 pm
Thank you Jack! I'll check it
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply