problems with cursors

[jitupatil2007]

hi friends i have created two cursors for fetching records from one table and updating column of second table but the problem i am facing is the cursors are very slow and i want to speed up the query see i have written the query like this :

[code language="SQL"]
--update cdr set destinationid = null

DECLARE @CDRID BIGINT;

DECLARE @CodeDestID BIGINT;

DECLARE @TempCodeDestID BIGINT;

DECLARE @CtryCode VARCHAR(20);

DECLARE @MATCH INT;

DECLARE @dialed_digits NVARCHAR(50);
DECLARE @FormattedNbr VARCHAR(50);
DECLARE @CountryCode VARCHAR(2);
DECLARE CDR_cursor CURSOR FOR SELECT CDRID, dialed_digits FROM mydata.dbo.CDR WHERE DestinationID IS NULL
OPEN CDR_cursor

FETCH NEXT FROM CDR_cursor INTO @CDRID, @dialed_digits

WHILE @@FETCH_STATUS = 0

BEGIN

SET @FormattedNbr = replace(LTRIM(RTRIM(@dialed_digits)), '"', '');

SET @CountryCode = substring(@FormattedNbr, 0, 3);

SET @CodeDestID = null;

SET @MATCH = 0;

DECLARE Dest_cursor CURSOR FOR SELECT DestinationID, Code FROM mydata.dbo.ctryCode WHERE code Like LTRIM(RTRIM(@CountryCode)) +'%' Order by code Desc

OPEN Dest_cursor

FETCH NEXT FROM Dest_cursor INTO @TempCodeDestID, @CtryCode

WHILE @@FETCH_STATUS = 0

BEGIN

IF @MATCH <> 1

BEGIN

SET @MATCH = PATINDEX(@CtryCode + '%', @FormattedNbr);

IF @MATCH = 1

BEGIN

print @TempCodeDestID

SET @CodeDestID = @TempCodeDestID

END
END

FETCH NEXT FROM Dest_cursor INTO @TempCodeDestID, @CtryCode

END

CLOSE Dest_cursor

DEALLOCATE Dest_cursor

IF @MATCH = 1

BEGIN

--Updating the maximum possible match

UPDATE mydata.dbo.CDR SET DestinationID = @CodeDestID WHERE CDRID = @CDRID

SET @CodeDestID = null;

SET @MATCH = 0;

END
FETCH NEXT FROM CDR_cursor INTO @CDRID, @dialed_digits

END

CLOSE CDR_cursor

DEALLOCATE CDR_cursor


SELECT mydata.dbo.CDR.CDRID, mydata.dbo.CDR.start_date_time, mydata.dbo.CDR.ani, mydata.dbo.CDR.dialed_digits, mydata.dbo.CDR.actual_dur, mydata.dbo.CDR.rounded_dur_secs,mydata.dbo.CDR.cos t, mydata.dbo.CtryCode.Destination, mydata.dbo.CtryCode.Code,

mydata.dbo.CtryCode.Cost AS Code_Rate

FROM mydata.dbo.CDR LEFT OUTER JOIN

mydata.dbo.CtryCode ON mydata.dbo.CDR.DestinationID = mydata.dbo.CtryCode.DestinationID

[/code]
can anybody help me please....its urgent

[ganeshkumar08]

Hello,

I am not very clear with your question, and can you send me the error what u are getting.
My suggestion is to declare both the cursors first on the top. And store the @Fetch_Status variable in temporary local variable.

Ganesh

[ck9663]

Did the error says CURSOR Dest_cursor is already declared or something like that?

It's because your cursor declaration for Dest_cursor is inside your loop. You have to put it outside the loop. You can still fetch it inside the loop.

Watch out for infinite loop.

-- CK