How to create function to calculate cost of product according to price prefix(+, -)

Question

I want to create a function in sql server that return the cost of product according to its price prefix. Price prefix either is"+" or "-".

MY code is:

  CREATE FUNCTION calculateOptionCost ( @product_price Decimal, @optionPrice Decimal,    @action varchar )
    RETURNS Decimal
    AS
    BEGIN
    IF (@action == '+')
    RETURN (@product_price + @optionPrice);
    IF (@action == '-')
    RETURN (@product_price - @optionPrice);
    END
    GO

In parameter, I have passed it's parent product price, its price and its price prefix. But when i execute this code the system give error as:

The last statement included within function must be a return statement.

score 1 · Answer 1 · answered Aug 12 '13 at 13:59

1

You have a path that does not return any value (if @action is not a + or a -)

Add RETURN @product_price as the last line and you will be fine

OR

assume that if the param is not a + it's a - and use ELSE

answered Aug 12 '13 at 13:59

asafrob

1,838
13
16

@OzrenTkalcecKrznaric - I'm just explaining the error. Personally I think that SQL functions aren't a very good idea – asafrob Aug 12 '13 at 14:03

score 1 · Answer 2 · edited Aug 13 '13 at 07:47

1

Try this:

CREATE FUNCTION calculateOptionCost (@product_price Decimal, @optionPrice Decimal, @action varchar)
RETURNS Decimal
AS
BEGIN
    Declare 
    @Result Decimal
    IF (@action == '+')
    Begin
        Set @Result = (@product_price + @optionPrice);
    End
    IF (@action == '-')
    Begin
        Set @Result = (@product_price - @optionPrice);
    End
    Return @Result
END
GO

edited Aug 13 '13 at 07:47

Vivek Jain

3,811
6
30
47

answered Aug 12 '13 at 13:59

Milen

8,697
7
43
57

OzrenTkalcecKrznaric · Answer 3 · 2013-08-12T14:11:14.923

Use this function if you wish:

CREATE FUNCTION calculateOptionCost (
    @product_price decimal, 
    @optionPrice decimal,    
    @action varchar)
RETURNS Decimal
AS
BEGIN
    DECLARE @res decimal
    IF (@action == '+')
        SET @res = @product_price + @optionPrice
    ELSE
        SET @res = @product_price - @optionPrice

    RETURN @res
END
GO

DECLARE @action char(1) = '-'
SELECT price1, price2, calculateOptionCost(price1, price2, @action)
FROM tbl1

However, I wouldn't use a function if I were you. There are better and more optimal methods for this.

I would suggest you to compare the execution time of function-based call with this expression, which does exactly the same:

SELECT price1, price2, calculate.OptionCost
FROM tbl1
CROSS APPLY (
    SELECT OptionCost = CASE
        WHEN @action = '+' THEN price1 + price2
        WHEN @action = '-' THEN price1 - price2
        ELSE NULL END
) calculate

However, I would simplify the logic: if '+' comes, think positive. Otherwise negative.

SELECT price1, price2, calculate.OptionCost
FROM tbl1
CROSS APPLY (
    SELECT OptionCost = CASE
        WHEN @action = '+' THEN price1 + price2
        ELSE price1 - price2 END
) calculate

Vivek Jain · Answer 4 · 2013-08-12T14:06:19.870

0

You need to make sure that the function returns values from all execution paths.

If both the if conditions are false (@action is neither + nor -) then what will the function return?

Return Product Price if both the conditions are not met.

CREATE FUNCTION calculateOptionCost ( @product_price Decimal, @optionPrice Decimal, @action varchar)
RETURNS Decimal
AS
BEGIN
    IF (@action == '+')
        RETURN (@product_price + @optionPrice);
    IF (@action == '-')
        RETURN (@product_price - @optionPrice);
    RETURN @product_price
END
GO

edited Aug 12 '13 at 14:06

answered Aug 12 '13 at 13:59

Vivek Jain

3,811
6
30
47

when i call this function as in query like "select TOP 100 opt.id, opt.option_name_id, opt.suffix, opt.stock, opt.cost, opt.price_prefix as prefix, opt.price as option_price, size.size_name, pro.sku, pro.product_name, pro.price as product_price, dbo.calculateOptionCost(product_price, option_price, prefix) as total_cost from ProductOptions as opt LEFT JOIN SizeOptions as size on size.id = opt.option_name_id INNER JOIN Products as pro on pro.product_id = opt.product_id" then this query give me error. It says: cannot find column 'dbo' or user defined func. pls give me way to exc it – user2393886 Aug 12 '13 at 16:30
@user2393886, seems as if you will have to alter your table to accommodate this function. Not sure if this is relevant in your case but this is what I got when I Google'd about it. http://stackoverflow.com/a/2769023/1654121, http://blogs.msdn.com/b/sqlcat/archive/2011/11/28/a-computed-column-defined-with-a-user-defined-function-might-impact-query-performance.aspx, http://benthurecht.blogspot.in/2011/07/creating-persisted-computed-columns.html and http://aldiriz.blogspot.in/2008/10/create-computed-column-using-user.html. Hope this helps! – Vivek Jain Aug 13 '13 at 06:40
You could also evaluate using `CROSS APPLY` as mentioned by @OzrenTkalcecKrznaric in his answer earlier. – Vivek Jain Aug 13 '13 at 06:51

How to create function to calculate cost of product according to price prefix(+, -)

4 Answers4