locked
creating a stored procedure RRS feed

  • Question

  • I have to right a stored procedure named "spAddPercentage" that adds a percentage passed as a parameter name "IncPercent" to the value of an order. I would like to use a statement IF/ELSE, something like if the value of "IncPercent" is under 10%, add that to the value of all orders, or if "IncPercent" is 10% or over, add 10% to the value of all orders, in the "OrderDetails" table. 

    Thanks.

    table OrderDetails.

    OrderRef

    CustRef

    DateOrdered

    Value

    DateDelivered

    1

    2

    24/11/2009

    $325.80

    29/11/2009

    2

    1

    24/11/2009

    $1340.45

    30/11/2009

    3

    8

    28/11/2009

    $988.50

    4/12/2009

    4

    4

    1/12/2009

    $2205.15

    5

    3

    6/12/2009

    $1540.25

    14/12/2009

    6

    9

    6/12/2009

    $965.20

    15/12/2009

    7

    9

    7/12/2009

    $756.50

    8

    5

    12/12/2009

    $309.45

    9

    6

    13/12/2009

    $1327.80

    Friday, August 24, 2018 11:39 AM

Answers

  • I have to right a stored procedure named "spAddPercentage" that adds a percentage passed as a parameter name "IncPercent" to the value of an order. I would like to use a statement IF/ELSE, something like if the value of "IncPercent" is under 10%, add that to the value of all orders, or if "IncPercent" is 10% or over, add 10% to the value of all orders, in the "OrderDetails" table. 

    If you would like to use IF...ELSE construct to achieve this, the script of the procedure "spAddPercentage" could be written like this.

    create proc spAddPercentage
    (
     @IncPercent numeric(10,2)
    )
    as
    begin
       IF(@IncPercent<10)
       update OrderDetails set Value=Value*(1+@IncPercent/100.0)
       ELSE
       update OrderDetails set Value=Value*(1+0.1)
    
    end

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Dandvv Wednesday, August 29, 2018 6:22 AM
    Monday, August 27, 2018 6:10 AM

All replies

  • something like this,

    Create Proc spAddPercentage ( IncPercent decimail(5,2))
    AS
    BEGIN 
    
    Update OrderDetails set Value = Value + ((Value *  case when IncPercent <10 then IncPercent else 10 end )/100) 
    
    END 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, August 24, 2018 11:55 AM
  • Create Proc spAddPercentage 
    @IncPercent Numeric(10,2)
    AS
    Update OrderDetails 
    set Value = Value *  (1 + (CASE WHEN @IncPercent > 10 THEN 10 ELSE @IncPercent END * .01))
    GO 


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, August 24, 2018 12:13 PM
  •  

    update OrderDetails
    set value = iif(@IncPercent*100/value < 10, value+@IncPercent, value+value*0.1)

    but i'm afraid is not quite simple i suppose  "value" type is varchar

    if so

    update OrderDetails

    set value = iif(@IncPercent*100/cast(REPLACE(value,'$','') as numeric) < 10, cast(REPLACE(value,'$','') as numeric)+@IncPercent, cast(REPLACE(value,'$','') as numeric)+cast(REPLACE(value,'$','') as numeric)*0.1)




    • Edited by ugabrielu Sunday, August 26, 2018 3:50 PM
    Sunday, August 26, 2018 3:19 PM
  • We have a few problems here. First of all, you fail to post any DDL. So we have to guess at everything from the keys, the constraints, the datatypes, etc. next, dates should be in the <g class="gr_ gr_44 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="44" id="44">yyyy</g>-mm-dd display format. It's the only one allowed in ANSI ISO standard SQL and it happens to be the most common international standard after the metric system. Tables do not include $'s; that's a display format that Sybase originally added to the proprietary money data type for the comfort of COBOL programmers who were used to having picture specs on their data declarations.

    I'm going to make an assumption that you should not have a customer reference in the order details. The customer and the whole order to have the relationship <g class="gr_ gr_51 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" data-gr-id="51" id="51">not</g> each individual item. Likewise, I'm going to assume that the order date (if you follow ISO 11179 rules, this column would have been named order_date) would belong to the order and not to each individual line item. Unfortunately, we have no way of knowing because you fail to post enough information.

    I think you're just copying the paper form.

    CREATE TABLE Orders
    (order_nbr CHAR(10) NOT NULL PRIMARY KEY,
     order_date DATE DEFAULT CURRENT_TIMESTAMP,
     order_customer_id CHAR(10)  NOT NULL
       REFERENCES Customers(customer_id),
     delivery_date DATE, -- null means not delivered
    );

    CREATE TABLE Order_Details
    (order_nbr CHAR(10) NOT NULL
      REFERENCES Orders(order_nbr)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
     item_gtin CHAR(15) NOT NULL --- industry-standard you should know
     unit_price DECIMAL (12,2) NOT NULL
       CHECK (unit_price >= 0.00),
     order_qty INTEGER NOT NULL
      CHECK (order_qty > 0)
    );

    >> I have to write a stored procedure named "spAddPercentage" that adds a percentage passed as a parameter name "<g class="gr_ gr_45 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="45" id="45">IncPercent</g>" to the value of an order. I would like to use a statement IF/ELSE, something like if the value of "<g class="gr_ gr_46 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="46" id="46">IncPercent</g>" is under 10%, add that to the value of all orders, or if "<g class="gr_ gr_47 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="47" id="47">IncPercent</g>" is 10% or over, add 10% to the value of all orders, in the "OrderDetails" table. <<

    <g class="gr_ gr_55 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="55" id="55">You do</g> know that the prefix "SP_" has special meaning in the T-SQL dialect? In RDBMS, it's a design error. Decades ago, when compilers were very simple and didn't have good symbol tables, Fortran, BASIC, and many proprietary languages had to put metadata into the names of the data elements for the compiler. Today, this makes as much sense as putting a buggy whip stand on a Ferrari.

    How do we add this 10%? We can't change the unit price on the items in the detail table, can we? This would mean would have to get to the inventory and do the updates on all of the items we carry. Or do you want to add it in the order details as a separate line item? Or do you want to create a special view, which shows the increase?

    SQL is a declarative language. Declarative languages do not use loops or if–then–else control flow; that is how we wrote in procedural languages.

    Can you get help from someone who knows SQL, and <g class="gr_ gr_48 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="48" id="48">post</g> a meaningful query request? Will we have now is too vague. Also, those colored stripes in your posting just look silly. We don't post pictures of <g class="gr_ gr_49 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-del replaceWithoutSep" data-gr-id="49" id="49">results,</g> but use DDL and DML instead. 





    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, August 26, 2018 7:45 PM
  • Also, those colored stripes in your posting just look silly. We don't post pictures of <g class="gr_ gr_49 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-del replaceWithoutSep" data-gr-id="49" id="49">results,</g> but use DDL and DML instead. 

    That's like the pot calling the kettle black

    I'm not sure what method you are using to post here but whatever it is it's causing a whole bunch of extra formatting information which makes your post illegible.

    Also please stop telling people to use yyyy-mm-dd as it is not a language safe format to use with the old SQL Server date types - remember this is a Microsoft SQL Server community so what works in this product is much more important than whether it follows some standard or other.

    Sunday, August 26, 2018 9:44 PM
  • I have to right a stored procedure named "spAddPercentage" that adds a percentage passed as a parameter name "IncPercent" to the value of an order. I would like to use a statement IF/ELSE, something like if the value of "IncPercent" is under 10%, add that to the value of all orders, or if "IncPercent" is 10% or over, add 10% to the value of all orders, in the "OrderDetails" table. 

    If you would like to use IF...ELSE construct to achieve this, the script of the procedure "spAddPercentage" could be written like this.

    create proc spAddPercentage
    (
     @IncPercent numeric(10,2)
    )
    as
    begin
       IF(@IncPercent<10)
       update OrderDetails set Value=Value*(1+@IncPercent/100.0)
       ELSE
       update OrderDetails set Value=Value*(1+0.1)
    
    end

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Dandvv Wednesday, August 29, 2018 6:22 AM
    Monday, August 27, 2018 6:10 AM