1

I want to be able to dynamically call a function that contains a select statement whose table changes depending on the function parameters. I know this is not possible to do within a function, but I have read that it is possible within a stored procedure. I made the following stored procedure to execute a string:

CREATE DEFINER=`uname`@`%` PROCEDURE `execute_Dynamic_Query`(IN dynamicQuery varchar(255), OUT resultValue varchar(255))
BEGIN
    SET @queryString =
        CONCAT(dynamicQuery);
    PREPARE stmt FROM @queryString;
    EXECUTE stmt;
    SET resultValue = @selectedVar;  
END

This function seems to work. When I call it, I see the expected value (1 returned row):

CALL control.execute_Dynamic_Query("SELECT date from test order by date desc limit 1;",@TestVar);

However, if I call this from within a function at all, the function suddenly stops working. For example, this works and returns "1":

CREATE DEFINER=`uname`@`%` FUNCTION `new_function`() RETURNS varchar(255) CHARSET latin1
BEGIN
    RETURN 1;
END

But this returns nothing and the "Message" that I see in Workbench is just "Ok" (with a green check mark).

CREATE DEFINER=`uname`@`%` FUNCTION `new_function`() RETURNS varchar(255) CHARSET latin1
BEGIN
    CALL control.execute_Dynamic_Query("SELECT date from test order by date desc limit 1;",@TestVar);
    RETURN 1;
END    

The ultimate goal is to return the the results in a function similar to this:

CREATE DEFINER=`uname`@`%` FUNCTION `new_function`() RETURNS varchar(255) CHARSET latin1
BEGIN
    DECLARE outputVar DATETIME;
    CALL control.execute_Dynamic_Query("SELECT date from test order by date desc limit 1;",@TestVar);
    outputVar = @TestVar;
    RETURN outputVar;
END    

I am using MySQL Workbench 6.3 and am disappointed with the unhelpful output that I'm seeing. Any ideas?

  • I don't think prepare+execute+deallocate was intended to be splayed across SPs and Functions. – Rick James Oct 3 '16 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.