MySQL function is returning wrong value

Question

I have a function with 4 parameters and for example if I set null/empty value for the first parameter which is type DATE I get the result 2 instead of 1.

SET @p0=''; SET @p1=''; SET @p2=''; SET @p3=''; SELECT `MY_FN`(@p0, @p1, @p2, @p3) AS `MY_FN`;

My code:

BEGIN
 IF PARAM1_DATE IS NULL or PARAM1_DATE='' THEN 
        SET @STATUS = 1;
    ELSE 
        SET @STATUS = 2;
    END IF;
    
    
    RETURN @STATUS;
END

I've tried also ISNULL(NULLIF(PARAM1_DATE,'') same result

Go on add rest of function..

P.Salmon
– P.Salmon

2021-01-27 17:15:48 +00:00
Commented Jan 27, 2021 at 17:15 — P.Salmon
– P.Salmon, Commented Jan 27, 2021 at 17:15

P.Salmon · Accepted Answer · 2021-01-27 17:24:59Z

1

the validation of parameters OCCURS BEFORE function code is executed you need to make sure a valid date (if that's what param1_date datatype is) is passed in the call to the function. if you want to pass any old rubbish then define as char and test in function..

answered Jan 27, 2021 at 17:24

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

MySQL function is returning wrong value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related