CODEWITHSUNDEEP
mysqlstored-procedures
Jon C.
Jon C.

Reputation: 374

MySQL (Stored) Procedure - parameters and query

I am trying to create a simple procedure with parameters.

CALL new_procedure('mode', 'ASC');

The first input is the column the second is the sort direction

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(IN in_order_by_column varchar(20), in_order_by_direction char(4))
BEGIN

    DECLARE order_by varchar(30);
    SET @order_by = CONCAT('`', in_order_by_column, '` ', in_order_by_direction);
/*
    SELECT * FROM `common_tags` ORDER BY @order_by  LIMIT 5;
*/
    SELECT @order_by as 'c';

END

In the above example I have it only outputting the 2 parameters so I can see what's happening.

Result:

"c"
`mode` ASC

.

When I run the procedure with it's intended code, below.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(IN in_order_by_column varchar(20), in_order_by_direction char(4))
BEGIN

    DECLARE order_by varchar(30);
    SET @order_by = CONCAT('`', in_order_by_column, '` ', in_order_by_direction);
    SELECT * FROM `common_tags` ORDER BY @order_by  LIMIT 5;

END

Results

tags_id     data                mode        parent_id       position
1           Wood                2           13              6
2           Trippy              0           0               0
4           Artists             1           0               1
6           "Newest Additions"  1           0               11
12          "Natural Elements"  2           5               8

As you can see the results are not sorted by mode.

Any help is appreciated.

Upvotes: 7

Views: 35488

Answers (1)

BlitZ
BlitZ

Reputation: 12168

Unfortunately, you need to PREPARE entire query in this case:

DELIMITER $$

DROP PROCEDURE IF EXISTS `new_procedure`$$

CREATE PROCEDURE `new_procedure`(IN in_order_by_column varchar(20), in_order_by_direction char(4))
BEGIN
    SET @buffer = CONCAT_WS('',
        'SELECT * FROM `common_tags` ORDER BY `', in_order_by_column, '` ', in_order_by_direction, ' LIMIT 5'
    );

    PREPARE stmt FROM @buffer;
    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

NOTE: Described approach should be used very careful, because it is vulnurable to SQL Injection attacks, if used incorrectly.

Upvotes: 11

Related Questions