As Manish has already implied, this is a scoping problem. The scope of a
cursor defined in a user session is for the entire session, whereas the
scope for the @variable is for a batch. So once the batch is terminated by
a "go" statement, your variable is out of scope, but your cursor is not, so
there is a conflict of scope here. Hence the requirement that DECLARE
CURSOR be the only statement in a batch. Cursor scope for a cursor declared
in a stored procedure is the scope of the procedure, which matches the scope
for any declared @variables in that procedure.
Having said all of that, you can still reproduce what you want with either a
#temp table, or with context variables. IE:
/* here is an example using appcontext */
set nocount on
go
declare c1 cursor for
select name from sysobjects where id = convert(int,
get_appcontext('c1','id'))
go
declare @silent int /* use to supress output */
select
@silent=rm_appcontext('c1','id'),@silent=set_appcontext('c1','id','8')
open c1
fetch c1
close c1
select
@silent=rm_appcontext('c1','id'),@silent=set_appcontext('c1','id','5')
open c1
fetch c1
close c1
go
------------------------------
syslogs
------------------------------
sysprocedures
Post by Jose LuisHi,
begin
declare
@account_no int
declare cmf_cur cursor for select * from CMF where
open cmf_cur
end
go
DECLARE CURSOR must be the only statement in a query batch.
but when I add "create procedure p1 as" it runs fine.
Any hint?
If you aren't in a proc you need to add a "go" (or other batch
separator) so that the declare cursor command is separate from the
open, fetch, etc.
Michael
declare
@account_no int
go
declare cmf_cur cursor for select * from CMF where
account_no=@account_no
go
With this code this error is shown:
isql -SDESA101_DS -DCUSTOMER101 -Udbo_billing -Pdbo_billing -
ikk.sql
Msg 137, Level 15, State 2:
Server 'DESA101_DS', Line 2:
Must declare variable '@account_no'.
Regards,
Jose Luis