CODEWITHSUNDEEP
mysql
Knight
Knight

Reputation: 13

MySQL CASE not working in Stored Procedure

This works fine. If I change the @unit_type variable. The correct unit type comes out in the calculation.

set @unit_type = 'kilometers';
set @lat = 50;
set @lon = 100;
set @lat2 = 51;
set @lon2 = 101;

select 
(CASE @unit_type 
    WHEN 'feet' THEN 20000000
    WHEN 'miles' THEN 3696.1
    WHEN 'meters' THEN 6979100
    WHEN 'kilometers' THEN 6979
    END
* acos( cos( radians(@lat) ) 
    * cos( radians( @lat2 ) ) 
    * cos( radians( @lon2 ) 
    - radians(@lon) ) 
    + sin( radians(@lat) ) 
    * sin( radians( @lat2 ) ) 
) ) AS distance;

This however, does not. When I try to run this stored procedure, it will return in kilometers every time. And if I delete the case for kilometers, the result is null.

CREATE PROCEDURE `anotherTest`(IN `lat` BIGINT, IN `lon` BIGINT, IN `lat2` BIGINT, IN `lon2` BIGINT, IN `unit_type` TEXT)
        LANGUAGE SQL
        NOT DETERMINISTIC
        CONTAINS SQL
        SQL SECURITY DEFINER
        COMMENT ''
    BEGIN

    select 
    (CASE @unit_type 
        WHEN 'feet' THEN 20000000
        WHEN 'miles' THEN 3696.1
        WHEN 'meters' THEN 6979100
        WHEN 'kilometers' THEN 6979
        END
    * acos( cos( radians(@lat) ) 
        * cos( radians( @lat2 ) ) 
        * cos( radians( @lon2 ) 
        - radians(@lon) ) 
        + sin( radians(@lat) ) 
        * sin( radians( @lat2 ) ) 
    ) ) AS distance;
    END

I just want to understand why this is, and how this would be better written to get around this issue.

Anyone have any ideas? Thanks in advance.

UPDATED:

The solution was to remove the @'s from within the SELECT statement of my procedure. As they are not required to access the parameters.

CREATE PROCEDURE `anotherTest`(IN `lat` BIGINT, IN `lon` BIGINT, IN `lat2` BIGINT, IN `lon2` BIGINT, IN `unit_type` TEXT)
        LANGUAGE SQL
        NOT DETERMINISTIC
        CONTAINS SQL
        SQL SECURITY DEFINER
        COMMENT ''
    BEGIN

    select 
    (CASE unit_type 
        WHEN 'feet' THEN 20000000
        WHEN 'miles' THEN 3696.1
        WHEN 'meters' THEN 6979100
        WHEN 'kilometers' THEN 6979
        END
    * acos( cos( radians(lat) ) 
        * cos( radians( lat2 ) ) 
        * cos( radians( lon2 ) 
        - radians(lon) ) 
        + sin( radians(lat) ) 
        * sin( radians( lat2 ) ) 
    ) ) AS distance;
    END

Upvotes: 0

Views: 254

Answers (1)

spencer7593
spencer7593

Reputation: 108450

@unit_type is a user variable. We don't see anywhere that is being set, in the procedure. (It may be set in the session preceding the call; it will have whatever value was last assigned to it in the session.)

We see the parameter passed in to the procedure is named unit_type. But we don't see any reference to that in the procedure.

One quick fix would be to set @unit_type user variable, immediately prior to the SELECT, to the value from the parameter:

SET @unit_type = unit_type;

The same issue exists for all the parameters as well, there's no reference to them either.

The other option to fix this would be to reference the procedure arguments in the SQL, replacing the references to the user_variables. (Looks like the quick edit there would be just to remove the @ characters.)

There's a significant difference between user-defined variables and procedure variables.

User-defined variables http://dev.mysql.com/doc/refman/5.5/en/user-variables.html

Upvotes: 1

Related Questions