How do I make ORDER BY work when creating a procedure in MySQL?

user_14400512 0
Viewed 63

I'm trying to create a procedure that inserts the data of a new lecturer, then shows the last 3 lecturers added. This is what my table (lecturers) looks like:

emp_id INT UNIQUE PRIMARY KEY,
first_name VARCHAR(20),
last_name VARCHAR(20),
faculty VARCHAR(3)

And this is my attempt to create the procedure:

DELIMITER //
    CREATE PROCEDURE new_lect (IN emp_id INT, first_name VARCHAR(20), last_name VARCHAR(20), faculty VARCHAR(3))
    BEGIN
        INSERT INTO lecturers (emp_id, first_name, last_name, faculty) VALUES (emp_id, first_name, last_name, faculty);
        SELECT * FROM lecturers
        ORDER BY emp_id DESC 
        LIMIT 3;
        END//
DELIMITER ;

Then I call the procedure with this data for example:

CALL new_lect(109,'Charlie','Smith','MAT');

However, ORDER BY does not seem to be doing its job because I always receive employee 100, 101, 102 instead of 107, 108, 109.

What am I doing wrong?

sql-order-by mysql sql
2 Answers

As a temporary (?) solution you may use prepared statement:

CREATE PROCEDURE new_lect (IN emp_id INT, first_name VARCHAR(20), last_name VARCHAR(20), faculty VARCHAR(3))
BEGIN
INSERT INTO lecturers (emp_id, first_name, last_name, faculty) 
    VALUES (emp_id, first_name, last_name, faculty);
PREPARE stmt FROM 'SELECT * FROM lecturers ORDER BY emp_id DESC LIMIT 3;';
EXECUTE stmt;
DROP PREPARE stmt;
END

fiddle (honestly stolen from Gordon Linoff's comment).

user_101387340

The source of a problem is found !

MySQL 8.0 Reference Manual / Stored Objects / Restrictions on Stored Programs / Name Conflicts within Stored Routines

The same identifier might be used for a routine parameter, a local variable, and a table column. Also, the same local variable name can be used in nested blocks.

In such cases, the identifier is ambiguous and the following precedence rules apply:

  • A local variable takes precedence over a routine parameter or table column.

  • A routine parameter takes precedence over a table column.

  • A local variable in an inner block takes precedence over a local variable in an outer block.

The behavior that variables take precedence over table columns is nonstandard.

In procedure's code ORDER BY emp_id is treated as ordering by procedure parameter IN emp_id INT which is constant.

So the procedure code must be

CREATE PROCEDURE new_lect (IN emp_id INT, first_name VARCHAR(20), last_name VARCHAR(20), faculty VARCHAR(3))
BEGIN
    INSERT INTO lecturers (emp_id, first_name, last_name, faculty) VALUES (emp_id, first_name, last_name, faculty);
    SELECT * FROM lecturers
    ORDER BY lecturers.emp_id DESC -- !!!!!
    LIMIT 3;
END
user_101387340
Related