Using dynamic SQL in MySql function

Question

I have created a function in phpMyAdmin as shown in this screenshotscreenshot.

When I try to use it like this:

Select DMax ("id","customers")

I get error #1305 saying that uTable does not exist. This is probably some basic syntax issue, as uTable in the sql statement is taken literally and not seen as a parameter. So how do I make it work?

Answer 1

You can't use parameters to a procedure for column or table names. Instead, you need to prepare a statement using those values and execute that. For example:

BEGIN
 DECLARE uValue INT(11);
 SET @sql = CONCAT('SELECT MAX(', uField, ') INTO uValue FROM ', uTable);
 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 RETURN uValue;
END

Note that you cannot use dynamic SQL in a function, so you would need to convert this into a stored procedure with uValue an OUT parameter i.e.

CREATE PROCEDURE DMax(
 IN uField VARCHAR(100),
 IN uTable VARCHAR(100),
 OUT uValue <appropriate type>
)
BEGIN
 DECLARE uValue INT(11);
 SET @sql = CONCAT('SELECT MAX(', uField, ') INTO uValue FROM ', uTable);
 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
END

Then you would need to call the procedure, something like

CALL DMax('table1', 'column1', @DMax)

and you can then

SELECT @DMax

(yes, this is a monumental pain)

• esims: When I try this, I get error #1336 Dynamic SQL is not allowed in stored function or trigger. Does this mean in general, or only on my server? I am not an administrator.
• Nick: @esims Sorry - that is a MySQL limitation. Please see my edit for how you can work around it.