The objective is to alter a query string within a Mysql stored procedure based on input variables.
Something like this:
CREATE DEFINER=`root`@`localhost` PROCEDURE `func`(type VARCHAR(15))
BEGIN
SET @type = type;
-- Check for the sort parameter
if @type="asc" THEN
SET @sort = " order by name asc";
elseif @type="desc" THEN
SET @sort = " order by name desc";
else
SET @sort ="";
end if;
SELECT id, name from table @sort;
END
Syntax for dynamic SQL is to make it string as below : 'SELECT statement'; To run a dynamic SQL statement, run the stored procedure sp_executesql as shown below : EXEC sp_executesql N'SELECT statement';
This blog will teach you how to create dynamic query in mysql using stored procedure. Dynamic SQL is a programming technique that enables us to write SQL statements dynamically at run time. Dynamic Queries are not embedded in the source program but stored as strings that are manipulated during program's run time.
MySQL supports Dynamic SQL with the help of EXECUTE and PREPARE statements. Suppose you have a scenario where you need to pass table name as parameter value and returns all column values, you can use Dynamic SQL. Let us create this table and data.
Dynamic queries refer to queries that are built dynamically by Drupal rather than provided as an explicit query string. All Insert, Update, Delete, and Merge queries must be dynamic. Select queries may be either static or dynamic. Therefore, "dynamic query" generally refers to a dynamic Select query.
The solution is to use execute, and concat:
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(input VARCHAR(15))
BEGIN
SET @input = input;
if @input="asc" then
SET @sort = " order by ActivityLogKey asc";
elseif @input = "desc" then
SET @sort = " order by ActivityLogKey desc";
else
SET @sort ="";
end if;
SET @query = CONCAT('select * from activitylog ',@sort,' limit 0, 5');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With