Procedure always returns empty column

Question

The following stored procedure returns nothing even if the matching data exists in the database.

DELIMITER $$

    DROP PROCEDURE IF EXISTS `portaldb`.`hasPrivateCloud`$$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `hasPrivateCloud`(in_userId int)
    begin
        if in_userId is not null then
            select (case when ui.privateCloud is null
                    then false
                    else ui.privateCloud
                    end) as hasPrivateCloud from userinfo as ui where ui.userid = in_userId;
        end if;
    end$$

    DELIMITER ;

privateCloud is a boolean field. If the field is false or where clause is not satisfied, the procedure should return false and when where clause is satisfied, it should return the column value. But it always returns an empty column.

What could be the reason for it?

Maybe this following stackoverflow post [display bit values in select](http://stackoverflow.com/questions/14248554/cant-see-mysql-bit-field-value-when-using-select) would help — g3suya, Nov 17 '16 at 11:21

fancyPants · Answer 1 · 2016-11-17T11:20:56.017

1

When the record doesn't exist, nothing is returned. Not, like you assume, a NULL record.

You could write it like

IF EXISTS (SELECT privateCloud FROM userinfo WHERE userid = in_userId)
    SELECT privateCloud FROM userinfo WHERE userid = in_userId;
ELSE
    SELECT 'false';
END IF;

When you're so concerned about performance, you can even do

IF EXISTS (SELECT @result := privateCloud FROM userinfo WHERE userid = in_userId)
    SELECT @result;
ELSE
    SELECT 'false';
END IF;

edited Nov 17 '16 at 11:20

answered Nov 17 '16 at 11:16

fancyPants

50,732
33
89
96

Alright. But this will return `true` even when `privateCloud` is false. – Suhail Gupta Nov 17 '16 at 11:17
Is there a better way, than running the query two times? – Suhail Gupta Nov 17 '16 at 11:18
It's no problem. The result will be in the query cache, when you use it, or the buffer pool. – fancyPants Nov 17 '16 at 11:20
@SuhailGupta And I added another possible way, using variables. – fancyPants Nov 17 '16 at 11:21

score 0 · Answer 2 · answered Nov 17 '16 at 11:50

Sorry about the earlier post. I deleted it. Thanks @fancyPants for pointing that out.

Thought i would add one more suggestion though

select (case when ifnull(ui.privateCloud,false)=false
                then 'False'
                else 'True'
                end) as hasPrivateCloud from userinfo as ui where ui.userid= in_userId;

Procedure always returns empty column

2 Answers2