Migration from Sybase to SQL 2000 problems

Hello,
I'm having a problem migrating a particular stored Procedure wich runs
nested and interdependent Fetching from temporary tables.
There is a main cursor and from that, a var is retrieved for use as a
parameter for other cursors. The most important is that the Cursor's queryes
all run successfully independently and with the parameters filled in.
//main cursor
declare CritSelAgr cursor for
select CodCritSelec, TodosSectInst, TodosPrzContr, TodosPais,
TodosMoeda, Coeficiente
from P_TRMCCSE0
where MRCUIC = @.PCodRub and
MRCUKN = @.PVersRub
order by CodCritSelec
for read only
select @.ErroSql = @.@.error
if @.ErroSql <> 0
begin
exec PCRMZZZZ0itratamsg @.ErroSql
return @.ErroSql
end
//dependent cursor *uses CodCritSelec from the main cursor
declare CritInstrFin cursor for
select CodInstrFin, CIF.CodTpConta, DescTpConta
from P_TRMCCIF0 CIF,
VRMCTCO00 TCO
where MRCUIC = @.PCodRub and
MRCUKN = @.PVersRub and
CodCritSelec = @.CodCritSelec and
CIF.CodTpConta = TCO.CodTpConta
order by CodInstrFin, TCO.CodTpConta
for read only
select @.ErroSql = @.@.error
if @.ErroSql <> 0
begin
exec PCRMZZZZ0itratamsg @.ErroSql
return @.ErroSql
end
In the stored procedure they do not work, only the main query, wich has all
parameters set works well.
//this is an extract of the code from the stored procedure
open CritSelAgr
select @.ErroSql = @.@.error
if @.ErroSql <> 0
begin
exec PCRMZZZZ0itratamsg @.ErroSql
return @.ErroSql
end
select @.SqlStat = 0
while( @.SqlStat = 0 )
begin
fetch CritSelAgr into @.CodCritSelec, @.TodosSectInst, @.TodosPrzContr,
@.TodosPais,
@.TodosMoeda, @.Coeficiente
select @.ErroSql = @.@.error, @.SqlStat = @.@.fetch_status
if @.ErroSql <> 0
begin
exec PCRMZZZZ0itratamsg @.ErroSql
return @.ErroSql
end
if @.SqlStat <> 0
break
select @.InstrFin = NULL, @.SectInst = NULL, @.PrzContr = NULL, @.Pais =
NULL, @.Moeda = NULL
open CritInstrFin
select @.ErroSql = @.@.error
if @.ErroSql <> 0
begin
exec PCRMZZZZ0itratamsg @.ErroSql
return @.ErroSql
end
select @.SqlStatCrit = 0
while( @.SqlStatCrit = 0 )
begin
fetch CritInstrFin into @.CodInstrFin, @.CodTpConta, @.DescTpConta
select @.ErroSql = @.@.error, @.SqlStatCrit = @.@.fetch_status
//*****@.@.fetch_status is ALWAYS -1 here and in the other cursors as well
Is there a way to pass explicitly the parameters to a cursor or it's enough
to have the vars defined in the scope? (I think that's the problem)
Thaks in advanceSince you are changing platforms, wouldn't now be a good time to get rid of
any legacy cursor code? I expect this could be done much more quickly and
efficiently with set-based code.
If you need more help, please post a fuller description of the problem as
explained in the following article:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Thanks for the reply,
Do you mean
Set @.var = value?
"David Portas" wrote:

> Since you are changing platforms, wouldn't now be a good time to get rid o
f
> any legacy cursor code? I expect this could be done much more quickly and
> efficiently with set-based code.
> If you need more help, please post a fuller description of the problem as
> explained in the following article:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||No. Set-based code means declarative SQL code that operates on entire sets o
f
rows. On the other hand, cursor code only processes one row at a time. SQL
Server is optimized for set-based code, not for cursors.
Without more information it's hard to guess how to do it in your case but
you should aim to write code using regular SELECT, UPDATE, INSERT and DELETE
statements *without* cursors. Cursors are rarely a good idea and most of the
time you don't need them.
David Portas
SQL Server MVP
--|||do you mean set based logic like in joins?
as for ex: in http://techrepublic.com.com/5100-6228_11-5532304.html
"David Portas" wrote:

> No. Set-based code means declarative SQL code that operates on entire sets
of
> rows. On the other hand, cursor code only processes one row at a time. SQL
> Server is optimized for set-based code, not for cursors.
> Without more information it's hard to guess how to do it in your case but
> you should aim to write code using regular SELECT, UPDATE, INSERT and DELE
TE
> statements *without* cursors. Cursors are rarely a good idea and most of t
he
> time you don't need them.
> --
> David Portas
> SQL Server MVP
> --|||Joins are set based operations, yes.
David Portas
SQL Server MVP
--