CODEWITHSUNDEEP
mysqllimit
Stev
Stev

Reputation: 27

how can I limit my query with the number that my variable _Limite passes?

I am trying to create this procedure stored in my MySql version 5.0 database but it does not allow me to create it.

the error is in "LIMIT _Limite" apparently this version does not accept this sentence.

how can I limit my query with the number that my variable _Limite passes?

BEGIN
    SELECT p.Id,
        Peso
    FROM pacas p
    INNER JOIN entradas e ON EntradaFK = e.Id
    WHERE FibraFK = _FibraFK
        AND PresentacionFK = _PresentacionFK
        AND PatioFK = _Patio
        AND e.Fecha <= _Fecha
        AND e.Estado = 'A'
        AND p.Estado = 'A'
    ORDER BY Id
    LIMIT _Limite;

END

Upvotes: 0

Views: 39

Answers (2)

Nae
Nae

Reputation: 15345

You can achieve this by using prepared statements.

SET _Limite := 1;

CALL statement(CONCAT('
    SELECT p.Id,
        Peso
    FROM pacas p
    INNER JOIN entradas e ON EntradaFK = e.Id
    WHERE FibraFK = _FibraFK
        AND PresentacionFK = _PresentacionFK
        AND PatioFK = _Patio
        AND e.Fecha <= _Fecha
        AND e.Estado = \'A\'
        AND p.Estado = \'A\'
    ORDER BY Id
    LIMIT ', _Limite, ';
    '))
    ;

prepared statement helper implementation: 

DELIMITER $$
CREATE PROCEDURE statement(IN dynamic_statement TEXT)
BEGIN
      SET @dynamic_statement := dynamic_statement;
      PREPARE prepared_statement FROM @dynamic_statement;
      EXECUTE prepared_statement;
      DEALLOCATE PREPARE prepared_statement;
  END;

DELIMITER ;

Upvotes: 0

Stev
Stev

Reputation: 27

I think it will be better to do it by direct consultation and not by stored procedure, since this version does not allow me to do it

Upvotes: 0

Related Questions