By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,453 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

problems with cursors

P: 6
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
Apr 23 '08 #1
Share this Question
Share on Google+
2 Replies


ganeshkumar08
P: 31
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
Apr 23 '08 #2

ck9663
Expert 2.5K+
P: 2,878
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
Apr 23 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.