0

I wanna know how to get field value from dynamic query. I do it on stored-procedure in MySQL. I've the following code: 

...
DECLARE l_query VARCHAR(500);
DECLARE l_table VARCHAR(50);

SET l_table = 'tb_user';
SET @l_query = concat('SELECT count(1) FROM ', l_table);
-- #Note that l_table will not always for tb_user,
-- it can be changed with other table name.

PREPARE l_sql FROM @l_query;
EXECUTE l_sql;
...


The question is, how to get value for count result (count(1)) ..?
I need this value, because it will be used on the next process at the same stored procedure.

Many thanks before.

Praditha
  • 1,162
  • 5
  • 24
  • 45
  • http://stackoverflow.com/questions/5591338/my-sql-dynamic-query-execute-and-get-ouput-into-a-variable-in-stored-procedure/5591433#5591433 – Devart Sep 03 '12 at 10:02

1 Answers1

1

Short: Use SELECT INTO to select the value into variable.

In your case:

...
DECLARE l_query VARCHAR(500);
DECLARE l_table VARCHAR(50);
DECLARE cnt INTEGER;

SET l_table = 'tb_user';
SET @l_query = concat('SELECT count(1) INTO @cnt FROM ', l_table);
-- #Note that l_table will not always for tb_user,
-- it can be changed with other table name.

PREPARE l_sql FROM @l_query;
EXECUTE l_sql;
-- Use cnt variable here
...
Sergey Kudriavtsev
  • 10,328
  • 4
  • 43
  • 68