MySQL - how to store execute result

Question

I wrote a procedure in mysql, which return a random value from a table. But I have a lot of tables, and I want to use this procedure many times, so I add as procedure parameter name table, from which I want to rand value, which name i will pass to the procedure also as an argument:

delimiter //

CREATE PROCEDURE randomDefVal(val varchar(50), tableName varchar(50),OUT randomVal varchar(50))

  BEGIN

  SET @tmpTableName = tableName;

  SET @sql_querry = concat('SELECT',@tmpVal,'FROM',@tmpTableName,'ORDER BY rand() LIMIT 1');

  PREPARE stmt FROM @sql_text;

  EXECUTE stmt using @randomVal;

  DEALLOCATE PREPARE stmt;       

END

But when I run this code I have an error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

Can anybody explain my how can I store the result of EXECUTE in a variable?

//SOLUTION

For those people how has the same problem as I had, I want to show an example solution which I have made:

delimiter //

CREATE PROCEDURE tmpProcedure(tableName varchar(20))

BEGIN

DECLARE my_query varchar(60);

DECLARE value varchar(20);

SET @my_query = concat('Select Name FROM ',tableName,' ORDER BY rand() LIMIT 1 INTO @outvar');

PREPARE stmt from @my_query;

EXECUTE stmt;

SET val = (SELECT @outvar); 

END//

I hope it will be helpful for you.

score 2 · Answer 1 · answered Apr 22 '13 at 10:37

I see a few errors in your code, especially regarding variable names beeing spelled differently or not declared at all. Check out this code which should be valid:

delimiter //

CREATE PROCEDURE randomDefVal(val varchar(50), tableName varchar(50),OUT randomVal varchar(50))

BEGIN

SET @tmpTableName = tableName;
SET @tmpVal = val;
SET @randomVal = randomVal;

SET @sql_querry = concat('SELECT',@tmpVal,'FROM',@tmpTableName,'ORDER BY rand() LIMIT 1');

PREPARE stmt FROM @sql_text;

EXECUTE stmt using @randomVal;

DEALLOCATE PREPARE stmt;

END 
//

I see what mistakes I have made. But all the time I have the same error. Maybe I call this procedure incorrect. I just made 'CALL randomDefVal('xyz','abc',@randomVal). How should I correct call this procedure and how can I have correct access to the randomVal in which I stored the result of execute? — Ziva, Apr 22 '13 at 11:23

MySQL - how to store execute result

1 Answers1