Re: creating view with user variable

From: Axel Schwenke <axel.schwenke_at_gmx.de>
Date: Fri, 27 Jan 2017 10:50:05 +0100
Message-ID: <o6f518$eos$1_at_dont-email.me>


Hi David,

On 26.01.2017 16:41, David wrote:
> So in case anyone else comes across this issue, here is how I did it using CONCAT()... leaving out a lot of the SQL (as I dont think its relevant
>
> DELIMITER $$
> CREATE PROCEDURE fetchClientOrders(IN AccountID INT(11), IN col VARCHAR(15), IN dir VARCHAR(4))
> BEGIN
> SET _at_accountID = AccountID;
> SET _at_col = col;
> SET _at_dir = dir;
> SET _at_sort = CONCAT('ORDER BY ',_at_col,' ',_at_dir,'');
> SET _at_query = CONCAT('SELECT orderID,orderDetails,package,submissionDate FROM userOrders WHERE acc_id = ',_at_accountID,' ',_at_sort,'');
> PREPARE stmt FROM _at_query;
> EXECUTE stmt;
> DEALLOCATE PREPARE stmt;
> END$$
>
> Dave.

Yes, this is exactly the way how to do it. That way of generating an SQL query as a string and then running it, is called /dynamic SQL/. In current MySQL versions you must use PREPARE + EXECUTE. MariaDB 10.2 (currently beta) implements EXECUTE IMMEDIATE which makes this a bit more straightforward.

https://mariadb.com/kb/en/mariadb/what-is-mariadb-102/

XL Received on Fri Jan 27 2017 - 10:50:05 CET

Original text of this message