Results 1 to 2 of 2

Thread: Store Procedure

  1. #1
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unanswered: Store Procedure

    This is an Oracle store procedure. Can Any body help me to convert it into SQL Server stored Procedure


    PROCEDURE CALC_PERC (DB_ID IN NUMBER, LAT_TYPE IN CHAR) IS
    Tot_work_all number(12,2);
    Bid_tot number(12,2);
    Ewo number(12,2);
    Overruns number(12,2);
    Underruns number(12,2);
    Contr_tot_all number(12,2);
    sContractType ae_contract.contr_type%type;
    BEGIN
    select sum(nvl(tamt_ret_item,0) + nvl(tamt_paid_item,0))
    into Tot_work_all
    from valid_item
    Where db_contract = db_id;
    Select sum(Contq * Contr_Price) into Bid_tot
    From Valid_item
    Where nvl(New_Item,'N') <> 'Y'
    and db_contract = db_id;
    Select sum(Qtd * Contr_price) into Ewo
    From Valid_item
    Where nvl(New_item,'N') = 'Y'
    and db_contract = db_id;
    Select Sum((Qtd-Nvl(Projq,0))*Contr_Price) into Overruns
    From Valid_item
    Where Qtd > Nvl(Projq,0)
    and db_contract = db_id
    and nvl(New_Item,'N') = 'N';
    IF LAT_type <> 'R' THEN
    Select Sum((Nvl(Projq,0)-Contq) * Contr_Price) into Underruns
    From Valid_item
    Where Nvl(Projq,0) < Contq
    and db_contract = db_id
    and nvl(New_Item,'N') = 'N';
    ELSE
    Select Sum((Nvl(Qtd,0)-Contq) * Contr_Price) into Underruns
    From Valid_item
    Where Nvl(Qtd,0) < Contq
    and db_contract = db_id
    and nvl(New_Item,0) = 'N';
    end if;
    Contr_tot_all:= NVL(Bid_tot,0) +NVL(ewo,0) +NVL(overruns,0)
    +NVL(underruns,0);

    IF Contr_tot_all = 0 THEN

    Select Contr_type into sContractType from ae_contract where db_contract = db_id;

    IF sContractType = 'A' OR sContractType = 'T' THEN
    --If the divisor is zero here, it's not an error.
    update ae_contract set perc_compu = 0 where db_contract = db_id;

    ELSE
    --If the divisor is zero here, it would be an error
    update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
    END IF;
    Else
    --Here we have a real number to calculate, so go ahead and do your stuff!
    update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
    END IF;
    END;
    mr_roomi

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Check your double-post [http://www.dbforums.com/t994851.html]
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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