Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to exit from a cursor Expand / Collapse
Author
Message
Posted Monday, March 9, 2009 12:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 20, 2014 3:06 AM
Points: 110, Visits: 319
hi all,
i have used a cursor. i want to stop execution of remaining rounds of cursour when a particular condition fails. To do this I had use
close cur_sd
deallocate cur_sd

but it is giving the error as
A cursor with the name 'cur_sd' does not exist.

can anybody help me to solve this issue

thanks in advance

regards
Durgesh J
Post #671267
Posted Monday, March 9, 2009 1:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:23 AM
Points: 40,620, Visits: 37,088
Post the code please?

My guess is you're referencing the cursor after it's deallocated.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #671272
Posted Monday, March 9, 2009 5:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 27, 2014 12:52 AM
Points: 2,031, Visits: 2,535
You can use BREAK statement. But as Gail told, it should be useful to you, if you post the code.

But i did some test to come out from the cursor. Here is the code

create table test
(
col1 varchar(10),
col2 varchar(10)
)
go
insert into test
select 'aa','ram'
union all
select 'bb','ram'
union all
select 'cc','ravi'
union all
select 'dd','rahi'
go

create proc p1
as
begin

declare @col1 varchar(10)

declare c1 cursor
for select col1 from test
for read only

open c1

fetch c1 into @col1

while @@fetch_status = 0

begin

if @col1 = 'cc'
break
select @col1

fetch c1 into @col1
end

close c1
deallocate c1
end

go

exec p1




karthik
Post #671367
Posted Tuesday, March 10, 2009 11:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 10:51 AM
Points: 442, Visits: 938
Simple. Add a loop continuation condition in your WHILE statement.

DECLARE @lb_Exit  bit
SET @lb_Exit = 0

WHILE (@@FETCH_STATUS = 0) AND (@lb_Exit = 0) BEGIN

IF your_condition SET @lb_Exit = 1

END

In fact, should @@ERROR become something else than 0 within the loop, I also use this method to exit the loop.
Post #672644
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse