SQL Server cursor needs to set the args upon open and not on declaration

Question

I have an old procedure written on Sybase and I need to migrate it to SQL Server. One of the problems I am facing is that in Sybase we usually write the declaration of the cursor using variables. And this variable will be set later, after the definition of the cursor, but before opening the cursor. So when the cursor opens the query will be based on the value of these variables that was set after declaration.

Example cursor CTSTRS_ACCDET is based on trs_no.

select @AL_TRSNO=10
    DECLARE CTSTRS_ACCDET CURSOR FOR
    SELECT  AC_BR,
            AC_CY,
            AC_GL,
            AC_CIF,
            AC_SL,
            FC_AMOUNT
    FROM CTSTRS_ACC_DET
    WHERE 
     AND TRS_NO = @AL_TRSNO
    FOR READ ONLY
select @AL_TRSNO=20

When the cursor is opened I need the query to retrieve the trx no 20 and not the 10.

Answer 1

A @table variable or #temp table to store the value of @AL_TRSNO and cursor selects from @table/#table:

declare @o int;
declare @t table(val int);

set @o = 22;
insert into @t(val) values(@o);

declare c cursor dynamic for
select name, object_id
from sys.all_columns
where object_id = (select max(val) from @t);

open c;
fetch next from c;
close c;

set @o = 19;
delete from @t;
insert into @t(val) values(@o);

open c;
fetch next from c;
close c;

deallocate c;