How to concatenate an SQL command into a Mysql Stored Procedure?

Question

In the following show, we have an example already running in PHP. The scenario of the question is the assembly of a "navigation link" based on a data structure.

$node = '006002009';  // Esta informação é obtida dinamicamente.

$segmentos = strlen($node)/3;
$sql = 'SELECT ACESSO_SCRIPT FROM ACESSO_ADMIN WHERE ACESSO_NIVEL';
$or = '';

Concatenation loop of the SQL command

for($x = 0; $x < $segmentos; $x++){  
$comp = $x+1;  
$sql.= $OR.'="'.substr($node,0,($indent*$comp)).'" ';  
$or = ' OR ACESSO_NIVEL';  
}

The result of the loop generates the string:

SELECT ACESSO_SCRIPT FROM ACESSO_ADMIN WHERE ACESSO_NIVEL="006" OR ACESSO_NIVEL="006002" OR ACESSO_NIVEL="006002009"`

The challenge is to achieve the same concatenation as the above loop within the Stored Procedure. Remembering that SP would receive as a parameter only the value of $node. This value is of variable length and follows a 3-digit segment composition rule.

Answer 1

I think something like this should solve your problem:

CREATE PROCEDURE batata(node CHAR(30))
BEGIN
    DECLARE segmentos INT;
    DECLARE jump INT;
    DECLARE contador INT;

    SET segmentos = LENGTH(node) / 3;
    SET jump = segmentos;
    SET contador = 1;

    SET @sql = 'SELECT ACESSO_SCRIPT FROM ACESSO_ADMIN WHERE ACESSO_NIVEL=';
    SET @sql = CONCAT(@sql, '"', node, '"', ' ');

    WHILE contador < segmentos DO
        SET @sql = CONCAT(@sql, 'OR ACESSO_NIVEL=', '"', SUBSTR(node, 1, jump), '"', ' ');

        SET contador = contador + 1;
        SET jump = jump * contador;
    END WHILE;

    PREPARE STMT FROM @sql;
    EXECUTE STMT;
END

---

Update:

How much logic has no secret is being used the same scheme as the example. I created a precedent that expects a variable that will be called char(30) (if you need a larger string just increase that number)

Inside the Procedure has 4 variables

segment calculating the number of segments of the Node (Node/3) jump variable used to take part of the contents of the string with substr accountant to control the while loop and sql which will contain the SQL string

after setting the @sql initial content (SET @sql = 'SELECT ACESSO_SCRIPT FROM ACESSO_ADMIN WHERE ACESSO_NIVEL=';)

I will use the mysql CONCAT command to concatenate the string according to the necessary logic.

After the loop the sql query will be complete,

ai just prepare the statement from the @sql variable and run

PREPARE STMT FROM @sql;
EXECUTE STMT;

if you want to debug and see the string just delete the lines prepare ... and execute ... and for select @sql;