How to concatenate a SQL statement inside a MySQL Stored Procedure?

Question

How to concatenate a SQL statement inside a MySQL Stored Procedure?

Navigation

#1 by (8 votes)

11

In the following example, we have an example already running in PHP. The scenario of the issue is the setting up 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 = '';

SQL command concatenation loop

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 of the above loop within the Stored Procedure. Recalling that the SP would receive as a parameter only the value of $node . This value is of variable length and follows a composition rule in 3-digit segments.

php mysql

asked by anonymous 16.03.2015 / 03:19

1 answer

How to bring the total of each column in the FooterRow of a jqGrid? Declaring a class with a reserved word name is a good idea?

score 8 · Accepted Answer

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:

As far as logic is concerned, the same schema is used as the example. I created a procedure that expects a variable that will be called char (30) 'node' (if you need a bigger string just increase that number)

Within the procedure there are 4 variables

segment that will calculate the number of segments of the 'node' (node / 3) jump variable used to pick up part of string content with substr counter to loop while and sql that will contain the SQL string

After setting the initial contents of @sql (SET @sql = 'SELECT ACCESS_SCRIPT FROM ACCESS_ADMIN WHERE ACCESS_NIVEL =';)

I'm using the CONCAT mysql command to concatenate the string according to the required logic.

After the loop, the sql query will be complete,

Then just prepare the statement from the @sql variable and execute

PREPARE STMT FROM @sql;
EXECUTE STMT;

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