help on cursors

[geffry]

Hi,

I am using sybase 12_1 on unix. I need to drop all procedures starting with a particular pattern.

declare @procname varchar(60)
declare proc_cursor cursor for select name from sysobjects where type = 'P' and name like 'abc%'
open proc_cursor
fetch next from proc_cursor into @procname
while (@@fetch_status <> -1)
begin
exec ('drop procedure ' + @procname)
fetch next from proc_cursor into @procname
end
deallocate proc_cursor
go


when i run this i get the following error:

DECLARE CURSOR must be the only statement in a query batch.
Msg 156, Level 15, State 1:
Server 'O', Line 4:
Incorrect syntax near the keyword 'from'.
Msg 137, Level 15, State 2:
Server 'O', Line 5:
Must declare variable '@@fetch_status'.
Msg 156, Level 15, State 1:
Server 'O', Line 9:
Incorrect syntax near the keyword 'from'.
Msg 102, Level 15, State 1:
Server 'O', Line 12:
Incorrect syntax near 'proc_cursor'.

thanks in advance

 

[saikrish]

Hi,
Try the code below.

Create procedure check_sp as
begin
declare @procname varchar(60)
declare proc_cursor cursor for select name from emp
open proc_cursor
fetch proc_cursor into @procname
while (@@sqlstatus = 0)
begin
exec('drop procedure' + @procname)
fetch proc_cursor into @procname
end
close proc_cursor
deallocate cursor proc_cursor
end

Vijay

 

[saikrish]

Hi,
Sorry, ignore the above reply and try this code.

create procedure check_sp as
begin
declare @procname varchar(60)
declare proc_cursor cursor for select name from sysobjects where type = 'P' and name like 'abc%'
open proc_cursor
fetch proc_cursor into @procname
while (@@sqlstatus = 0)
begin
exec('drop procedure' + @procname)
fetch proc_cursor into @procname
end
close proc_cursor
deallocate cursor proc_cursor
end

Vijay

 

[geffry]

Thanks Vijay.. It worked.

Cursors should be used as part of Procedures ?