CODEWITHSUNDEEP
mysqlphpmyadmin
esims
esims

Reputation: 420

Using dynamic SQL in MySql function

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?

Upvotes: 0

Views: 1906

Answers (1)

Nick
Nick

Reputation: 147166

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)

Upvotes: 1

Related Questions