Click here to Skip to main content
14,668,908 members
Rate this:
Please Sign up or sign in to vote.
See more:
I try to delete multiple duplicates using sp and cursor, but it throw error

What I have tried:

creat proc uspfixdup
(
  @paramID int
)
as


declare @id int
declare _cursor CURSOR LOCAL FAST_FORWARD FOR
select  id
from departments dp
where dp.roleid=1
group by id
having count(*)>1
order by id desc

open _cursor

fetch next FROM _cursor INTO @id

while @@FETCH_STATUS = 0
BEGIN
print @id

 exec uspfixdup @paramID = @id


fetch next FROM _cursor INTO @id

END

close _cursor
DEALLOCATE _cursor


DECLARE @deptID1 int
DECLARE @deptID2 int

SELECT @deptID1 = deptID
FROM   departments 
WHERE  ID = @paramID
AND    RoleID = 1


SELECT deptID2 = deptID
FROM   departments 
WHERE  ID = @paramID
AND    RoleID = 1
AND    deptID <> @deptID1


IF @deptid1 >1 AND @deptID2>1
BEGIN

  IF @depti1 < @deptID2
    DELETE FROM departments  WHERE deptID = @detID1
  ELSE
    DELETE FROM departments  WHERE deptID = @deptID2
END
ELSE
  PRINT 'no duplicate '
GO
Posted
Updated 23-Sep-20 19:19pm
v2
Comments
Suvendu Shekhar Giri 8-Jul-16 2:42am
   
I don't think it needs so much line of code to delete duplicate in any case.
Can you please share your requirement clearly so that we may be able to suggest you a better approach?
ZurdoDev 8-Jul-16 23:08pm
   
The error should be pretty clear. Your sp is calling itself over and over recursively and sql only allows 32 calls that way.
Member 12612576 9-Jul-16 7:16am
   
Thanks I fixed now

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

As mentioned in the comments, the error means you are calling your stored procedure recursively over and over too many times. You should evaluate if a cursor is necessary and even more so if a recursive function is actually necessary. Both are bad ideas generally speaking.

Glad to see you got it working though.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100