pl sql column name as parameter

Question

this is the stored procedure:

DELIMITER $$
CREATE PROCEDURE select_vehicles(IN name_of_column VARCHAR(255))
BEGIN
    SELECT * FROM vehicle order by name_of_column;
END
$$ DELIMITER ;

The code has no errors, but the "order by " is not working.

how can i pass column names as a parameter and work with them

Answer 1

You need dynamic SQL to pass a column name as a paramater. So something like:

DELIMITER $$

CREATE PROCEDURE select_vehicles(IN name_of_column VARCHAR(255))
BEGIN
    SET @sql = CONCAT('SELECT * FROM vehicle order by `', name_of_column, '`');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
$$