Mysql Function returns Null always

Question

I have a function in mysql like below:

DELIMITER $$ 

CREATE DEFINER=root@localhost FUNCTION fnGetDropDownValue(
    itemValue varchar(300), 
    DropDownId int, 
    CId int
) RETURNS int(11)
BEGIN
    DECLARE listId int;
    SELECT ListID into listId FROM DropDownListValues WHERE LOWER(ListValue) = LOWER(itemValue) AND DropDownListID = DropDownId AND (ClientId = 0 OR ClientId = CId);
    RETURN listId;
END$$

But it always returns Null values when I use
SELECT fnGetDropDownValue('General', 24, 18); I don't know what I am doing wrong :(

Have you checked your error log? What errors do you get? What steps have you taken to troubleshoot this? — John Conde
– John Conde, Commented Mar 1, 2013 at 16:18
Have you tried your query direct in sheel? Does it return it value? — Hamlet Hakobyan
– Hamlet Hakobyan, Commented Mar 1, 2013 at 16:21
I used to check executing select query in a separate window and it gives result — balakrishnan
– balakrishnan, Commented Mar 1, 2013 at 16:21
What happens if you replace the select query in the function with a simple SELECT 5 INTO listId? Does it still give you NULL? — Alanyst
– Alanyst, Commented Mar 1, 2013 at 16:25
Thanks for the help Alanyst i noticed in windows it as case insensitive. I changed variable listId to listId1 and now it works. — balakrishnan
– balakrishnan, Commented Mar 4, 2013 at 8:25

marc_s · Accepted Answer · 2019-05-08 20:34:20Z

1

After having the case sensitive issue with mysql columns I used to have variable names to start with _ to avoid it messing with column names. Now the stored procedure looks like this:

DELIMITER $$ 

CREATE DEFINER=root@localhost FUNCTION fnGetDropDownValue(
    itemValue varchar(300), 
    DropDownId int, 
    CId int
) RETURNS int(11)
BEGIN
    DECLARE _listId int;
    SELECT ListID into _listId FROM DropDownListValues WHERE LOWER(ListValue) = LOWER(itemValue) 
        AND DropDownListID = DropDownId AND (ClientId = 0 OR ClientId = CId);
    RETURN _listId;
END$$

This way it will work on any platform and it may be useful for others.

edited May 8, 2019 at 20:34

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 6, 2019 at 13:34

balakrishnan

3835 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Mysql Function returns Null always

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