SQL Server if else statements not working correctly

Question

I have created user defined function in SQL Server, but somehow the logic is not correct, it always executes the else statement; please help!

CREATE FUNCTION shipSelection
    (@Item_No varchar(20),@itemname varchar(20))
RETURNS varchar(100)
AS
BEGIN
    declare @weight float
    declare @demention float
    declare @density float
    declare @result varchar(100)

    SET @weight = (SELECT Item_Weight FROM Items 
                   WHERE Item_No = @Item_No AND Name = @itemname) 
    SET @demention = (SELECT Dimention FROM Items 
                      WHERE Item_No = @Item_No AND Name = @itemname)

    SELECT @density = @weight / @demention

    IF @density > 100
    BEGIN
        SET @result = 'LOW DENSITY CAN USE ANY TYPE SHIPS';
    END
    ELSE IF @density > 300
    BEGIN
       SET @result = 'MEDIUM DENSITY CAN USE MEDIUM SHIPS';
    END
    ELSE IF @density > 500
    BEGIN
        SET @result = 'MEDIUM DENSITY WANT TO USE HUGE SHIPS';
    END
    ELSE 
    BEGIN
       SET @result = 'DONT WANT TO CARE ABOUT SHIPS TOO SMALL DENSITY ITEM';
    END

    RETURN @result
END

SELECT 
    dbo.shipSelection('I010', 'asus x555l') AS TRANSPORT_SHIP_TYPE

I suggest that you add some debug code to select the whole record of to show the result when you populate dimension and weight — Mike
– Mike, Commented Jul 23, 2016 at 19:44

user5886152 · Accepted Answer · 2016-07-23 19:54:10Z

4

The way you have the logic, it's always going to hit either the first case (> 100), or the else (< 100). Try checking the largest numbers first.

It will never hit the > 300 case, because it will use the > 100 case first.

If you are always hitting the else statement, you'll need to check that your value is actually greater than 100. Sounds like it isn't.

answered Jul 23, 2016 at 19:54

user5886152

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Over a year ago

Thanks for getting back to us that it fixed your problem. It is customary to accept the correct answer ( @PhillipXT) so that he gets the credit and to help future users with a similar problem to find the correct answer.

Ross Bush · Accepted Answer · 2016-07-23 20:10:15Z

0

I bet that one of your queries returns a NULL value. Since null is not a number and cannot be compared with a number your else statement, in the case of null, will be hit. You can prevent this by setting the value of the query returning null to a known value using COALESCE.

    SET @weight = (SELECT COALESCE(Item_Weight,0) FROM Items 
                   WHERE Item_No = @Item_No AND Name = @itemname) 
    SET @demention = (SELECT COALESCE(Dimention,0) FROM Items 
                      WHERE Item_No = @Item_No AND Name = @itemname)

answered Jul 23, 2016 at 20:10

Ross Bush

15.2k2 gold badges39 silver badges65 bronze badges

4 Comments

user5886152 Over a year ago

The demention variable should maybe default to 1, because he's doing division with it afterwards.

Ross Bush Over a year ago

Personally, I prefer checking for divide by zero condition with a CASE statement rather than setting the value using COALESCE. If @demention is used after the division then you will need to keep a mental tally of it being 1 instead of zero. Just my two cents.

Ross Bush Over a year ago

SELECT @a=CASE WHEN @c=0 THEN 0 ELSE @b/ @c END...however you are not wrong and I am not right:)

user5886152 Over a year ago

As long as it's checked for before the division, it's all good :)

Collectives™ on Stack Overflow

SQL Server if else statements not working correctly

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related