mysqlprepared-statementcase-when

Executing different prepared statement based on conditions of case when clause


I'm quite a beginner to MySQL and I am trying to execute different prepared statements based on the conditions in case when clause. The problem is to find the median of LAT_N column of the table STATION and I want to use Case when for even and odd number of LAT_N records.

This one is my prepared statement for odd case:

SET @foo = (SELECT ROUND((COUNT(*)-1)/2) FROM STATION);
PREPARE STMT1 FROM 'SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1';
EXECUTE STMT1 USING @foo;

And this is for the even:

SET @foo1 = (SELECT ROUND(COUNT(*)/2) FROM STATION);
SET @foo2 = (SELECT ROUND((COUNT(*)+2)/2) FROM STATION);
PREPARE STMT2 FROM 'SELECT ROUND((((SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1) + (SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1))/2),4)';
EXECUTE STMT2 USING @foo1, @foo2;

And my idea is a bit similar to this code:

SELECT CASE WHEN COUNT(*)%2=0 THEN EXECUTE STMT2 USING @foo1, @foo2
            ELSE EXECUTE STMT1 USING @foo
FROM STATION
END;

But that code returns error 1064 (42000) so I wonder if there's any alternative for it. Thanks a lot


Solution

  • I've found the answer for this, which includes the use of procedure

    DELIMITER $$
    
    CREATE PROCEDURE latmed()
    BEGIN 
    IF (SELECT COUNT(*) FROM STATION)%2=0 THEN
        SET @foo1 = (SELECT ROUND(COUNT(*)/2) FROM STATION);
        SET @foo2 = (SELECT ROUND((COUNT(*)+2)/2) FROM STATION);
        PREPARE STMT2 FROM 'SELECT ROUND((((SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1) + (SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1))/2),4)';
        EXECUTE STMT2 USING @foo1, @foo2;
    ELSE
        SET @foo = (SELECT ROUND((COUNT(*)-1)/2) FROM STATION);
        PREPARE STMT1 FROM 'SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1';
        EXECUTE STMT1 USING @foo;
    END IF;
    END$$
    
    DELIMITER ;
    CALL latmed()
    

    I think it's a bit long so if anyone has a shorter code can answer my question. Thanks