SQL command in String Mysql

Question

I am making a precedent that saves a select in a varchar variable. Now I need to run this sql command, how do I do that? I am using Mysql. Procedure:

delimiter $$
create procedure eixos_caminhao (in numeroEixos int)
declare comando varchar(500);
    set comando = 'select classe, velocidade, date(data) as data, pesoEixo1, pesoEixo2';
    if (numeroEixos > 2) set comando = concat(comando, ', pesoEixo3');
    if (numeroEixos > 3) set comando = concat(comando, ', pesoEixo4');
    if (numeroEixos > 4) set comando = concat(comando, ', pesoEixo5');
    if (numeroEixos > 5) set comando = concat(comando, ', pesoEixo6');
    if (numeroEixos > 6) set comando = concat(comando, ', pesoEixo7');
    if (numeroEixos > 7) set comando = concat(comando, ', pesoEixo8');
    if (numeroEixos > 8) set comando = concat(comando, ', pesoEixo9');
    set comando = concat(comando. ' from tb_vbv');
end$$
delimiter;

Answer 1

Use the commands PREPARE and EXECUTE:

PREPARE myquery FROM comando;
EXECUTE myquery;

You can search for more links like this.

delimiter $$
create procedure eixos_caminhao (in numeroEixos int)
begin
    set @comando := 'select classe, velocidade, date(data) as data, pesoEixo1, pesoEixo2';
    if (numeroEixos > 2) set @comando:= concat(@comando, ', pesoEixo3');
    if (numeroEixos > 3) set @comando:= concat(@comando, ', pesoEixo4');
    if (numeroEixos > 4) set @comando:= concat(@comando, ', pesoEixo5');
    if (numeroEixos > 5) set @comando:= concat(@comando, ', pesoEixo6');
    if (numeroEixos > 6) set @comando:= concat(@comando, ', pesoEixo7');
    if (numeroEixos > 7) set @comando:= concat(@comando, ', pesoEixo8');
    if (numeroEixos > 8) set @comando:= concat(@comando, ', pesoEixo9');
    set @comando:= concat(@comando, ' from tb_vbv');

    PREPARE myquery FROM @comando;
    EXECUTE myquery;
end;
delimiter;

Answer 2

To execute a dynamic command within a function, procedure or in trigger it is necessary to prepare the string and then execute her.

For example, to create a precedent that sums two numbers and returns the result would look something like this:

DELIMITER $$

DROP PROCEDURE IF EXISTS soma $$ 

CREATE PROCEDURE soma(in valor1 INT(11), in valor2 INT(11))
BEGIN
    PREPARE stmt FROM CONCAT('SELECT ', valor1,' + ', valor2, ' AS resultado');
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$

DELIMITER;

To return the result just run something like:

CALL soma(1, 2);

In this case the return will be:

+---------+
|resultado|
|---------|
|        3|
+---------+

This same function can also be changed so that parameters are passed. The advantage of passing parameters is that you can reuse it query at some point within the procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS soma $$ 

CREATE PROCEDURE soma(in valor1 INT(11), in valor2 INT(11))
BEGIN
    SET @valor1 = valor1, @valor2 = valor2; # É necessário criar essas variáveis antes de utiliza-las no USING
    PREPARE stmt FROM 'SELECT ? + ? AS resultado';
    EXECUTE stmt USING @valor1, @valor2;
    DEALLOCATE PREPARE stmt;
END $$

DELIMITER;

If passed the same parameters the return will be exactly the same.

You can see more about prepared statments here.

Explanation of Commands

• PREPARE nome_da_variavel FROM sql: At this point the query is being prepared, that is to say the variable nome_da_variavel will receive a statment with the query which was passed after the FROM;
• EXECUTE nome_da_variavel: The EXECUTE will execute the statment. If you have parameters, after the statment the word reserved should be used USING and after it, all parameters separated by comma.
• DEALLOCATE PREPARE nome_da_variavel: The DEALLOCATE is used to release the variable, that is, after its execution, the statment will no longer exist.