CODEWITHSUNDEEP
mysqlfunction
balakrishnan
balakrishnan

Reputation: 383

Mysql Function returns Null always

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 :(

Upvotes: 1

Views: 2830

Answers (1)

balakrishnan
balakrishnan

Reputation: 383

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.

Upvotes: 1

Related Questions