Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: Simple Cursor Example

    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
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow Sp ?

    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
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Great!!! Ill try to modify this for what I need. Thanx for the help.
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Don't you have to close the cursor?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Umm...I dunno....
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •