March 9, 2009 at 12:54 am
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
March 9, 2009 at 1:11 am
Post the code please?
My guess is you're referencing the cursor after it's deallocated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2009 at 5:51 am
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
March 10, 2009 at 11:22 am
Simple. Add a loop continuation condition in your WHILE statement.
[font="Courier New"]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[/font]
In fact, should @@ERROR become something else than 0 within the loop, I also use this method to exit the loop.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply