Mysql dynamically build query string in a stored procedure based on logic

Question

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

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 — Ben Guthrie
– Ben Guthrie, Commented Dec 18, 2011 at 3:57
If this is the solution why you didn't add it as an answer and accept it or edit your question with it. — Mahmoud Gamal
– Mahmoud Gamal, Commented Dec 18, 2011 at 11:51
Without 100 reputation, I have to wait 8 hours to answer my own question — Ben Guthrie
– Ben Guthrie, Commented Dec 18, 2011 at 17:10
@BenGuthrie: Then wait. The limitation wasn't put into place because the devs want you to post your answer in the wrong location; it was put into place because they want you to wait. There are reasons for this. — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Dec 18, 2011 at 17:13

Ben Guthrie · Accepted Answer · 2011-12-18 17:11:14Z

25

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

answered Dec 18, 2011 at 17:11

Ben Guthrie

9792 gold badges8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ben Guthrie Over a year ago

The usefulness I found for this is to be able to conditionally insert joins, fields, or any other other SQL code as part of the stored proc rather than in PHP. It seems far cleaner to implement this at the stored proc level.

ToolmakerSteve Over a year ago

BTW, execute and concat are solution even if not defining a stored procedure; good example under "But if you don't change... implement a prepared statement to generate dynamic SQL." in Taryn's answer here. I find this useful for "throwaway" (one-time) queries.

Collectives™ on Stack Overflow

Mysql dynamically build query string in a stored procedure based on logic

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related