Simple Cursor Example

JDionne · #1 (**permalink**) 12-02-04, 14:16

Can anyone post a simple cursor example. I have the cursor in SQL Server Code but im having a bit of trouble converting it.

Code:

declare @UnitNumber10 as Varchar(10), @UnitNumber11 as Varchar(11), @CalcTotal as int, @CheckDigit as varchar(2)

declare UnitList cursor 
for
select distinct CntrNumber
from STG_RAIL_MOVE_PLAN_456_TBL
option (maxdop 4)

OPEN UnitList

FETCH NEXT FROM UnitList 
INTO @UnitNumber10

WHILE @@FETCH_STATUS = 0
BEGIN

set @CalcTotal = ((select Numeric_Return_Value from REF_CHECKDIGIT_LU_TBL where Alpha_Code = substring(@UnitNumber10,1,1))
     + (2*(select Numeric_Return_Value from REF_CHECKDIGIT_LU_TBL where Alpha_Code = substring(@UnitNumber10,2,1)))
     + (4*(select Numeric_Return_Value from REF_CHECKDIGIT_LU_TBL where Alpha_Code = substring(@UnitNumber10,3,1)))
     + (8*(select Numeric_Return_Value from REF_CHECKDIGIT_LU_TBL where Alpha_Code = substring(@UnitNumber10,4,1)))
     + (16*substring(@UnitNumber10,5,1)) 
     + (32*substring(@UnitNumber10,6,1))
     + (64*substring(@UnitNumber10,7,1))
     + (128*substring(@UnitNumber10,8,1))
     + (256*substring(@UnitNumber10,9,1))
     + (512*substring(@UnitNumber10,10,1)) )

set @CheckDigit = case when @CalcTotal%11 = 10 then 0 else @CalcTotal%11 end
set @UnitNumber11 = @UnitNumber10 + @CheckDigit


update STG_RAIL_MOVE_PLAN_456_TBL
set CntrNumber11Digit = @UnitNumber11
where CntrNumber = @UnitNumber10
option (maxdop 4)

FETCH NEXT FROM UnitList into @UnitNumber10

END

CLOSE UnitList

DEALLOCATE UnitList

Tank · #2 (**permalink**) 12-03-04, 06:59

Hi I'm guessing that you want to create, fill and return the contens
of a cursor i a Stred Procedure. Heres a (working) example:

create procedure db2inst2.s_patoopslag ( in p_cpr char (10) )

specific patoopslag_v3

dynamic result sets 1

reads sql data not deterministic

language sql

a:begin

declare cur_1 cursor with return for

select x.k_serviceyderid, x.k_rekvnr, x.v_cprnr, x.c_rekvydernr, x.c_rekvafd,
x.d_rekvdato, x.d_modtdato, x.d_svardato, y.c_mattypecd, z.k_snomed, z.v_txtll, substr(z.c_fritekst,1,50) as fritekst, z.k_matnr, z.k_sekvensnr
from pat.t_rekv x, pat.t_lprp y, pat.t_diag z
where x.k_serviceyderid=y.k_serviceyderid and x.k_rekvnr=y.k_rekvnr
and x.k_serviceyderid=z.k_serviceyderid and x.k_rekvnr=z.k_rekvnr and x.v_cprnr = p_cpr;

open cur_1;

end a

Hope you can benefit

JDionne · #3 (**permalink**) 12-03-04, 08:21

Great!!! Ill try to modify this for what I need. Thanx for the help.

Brett Kaiser · #4 (**permalink**) 12-03-04, 10:29

Don't you have to close the cursor?

JDionne · #5 (**permalink**) 12-03-04, 12:11

Umm...I dunno....

Internet Services

Web Development

Digital Marketing

Consumer Tech