mysql select case not working with function

Question

how to store value in image1 variable

BEGIN
DECLARE image1 VARCHAR(250);
select case when (
            (
                select COUNT(*)
                from profile_images
                where building_id = bid
                    and contractor_id = cid
                ) > 0
            ) then (
                select distinct (image)
                into image1
                from profile_images
                where building_id = bid
                    and contractor_id = cid limit 1
                ) else (
            select distinct (image)
            into image1
            from profile_images
            where contractor_id = cid limit 1
            )

END
RETURN image1;
END

score 1 · Accepted Answer · answered Aug 02 '17 at 18:47

1

Try:

BEGIN
DECLARE image1 VARCHAR(250);
select case when
            (
                select COUNT(*)
                from profile_images
                where building_id = bid
                    and contractor_id = cid
                ) > 0
              then (
                select distinct (image)
                -- into image1
                from profile_images
                where building_id = bid
                    and contractor_id = cid limit 1
                ) else (
            select distinct (image)
            -- into image1
            from profile_images
            where contractor_id = cid limit 1
            )
END into image1;
RETURN image1;
END

answered Aug 02 '17 at 18:47

wchiquito

16,177
2
34
45

The answer is useful – samjad ps Aug 03 '17 at 01:10
its working under local sever but main server ishows error with message "#2014 - Commands out of sync; you can't run this command now" @wchiquito – samjad ps Aug 03 '17 at 03:45
@samjadps: Check [B.5.2.14 Commands out of sync](https://dev.mysql.com/doc/refman/5.7/en/commands-out-of-sync.html). – wchiquito Aug 03 '17 at 06:58

mysql select case not working with function

1 Answers1