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(R TRIM(@dialed_di gits)), '"', '');

SET @CountryCode = substring(@Form attedNbr, 0, 3);

SET @CodeDestID = null;

SET @MATCH = 0;

DECLARE Dest_cursor CURSOR FOR SELECT DestinationID, Code FROM mydata.dbo.ctry Code WHERE code Like LTRIM(RTRIM(@Co untryCode)) +'%' 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(@CtryC ode + '%', @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_sec s,mydata.dbo.CD R.cost, mydata.dbo.Ctry Code.Destinatio n, mydata.dbo.Ctry Code.Code,

mydata.dbo.Ctry Code.Cost AS Code_Rate

FROM mydata.dbo.CDR LEFT OUTER JOIN

mydata.dbo.Ctry Code ON mydata.dbo.CDR. DestinationID = mydata.dbo.Ctry Code.Destinatio nID

[/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