How to use 'limit' inside a MYSQL database

Asked

Viewed 188 times

0

Talk about beauty?

Next, I am mounting a precedent, where I insert as parameter the number of lines that it will return and if it is 0 or is empty, it returns all possible lines, but in my logic is giving syntax error and so I needed your opinion. Follow what I’ve done:

CREATE DEFINER=`root`@`localhost` PROCEDURE `consultarPostagens`(
in cat varchar(40),
in limite int(11)
)

BEGIN
    select * from tb_post where
        (   case when cat is null or cat = '' then categoria is not null
            else categoria = cat end) and
        (   case when limite > 0 or limite <> '' then limit limite end )
END

In short, Procedure returns all posts and if a category is set, it brings all posts from that category.

The problem is on the edge, I do not know why I can not put within the case the term 'limit', he of the syntax error.

inserir a descrição da imagem aqui

If anyone has any better idea of how to do or how to write this command, I would appreciate it.

Valeus!

mysql procedure

  • I told you it wouldn’t work without looking at the code. I intend to remove my answer, you can accept yours even after, or that of someone who provides the correct answer. I cannot delete with it accept.

  • But your answer made me think beyond... I’m new to the programming world and sometimes I need these triggers to solve certain problems. But thank you very much.

1 answer

1


I decided as follows:

I created a variable and made a condition that according to the result that comes from the parameter it assigns a certain value to variable and then use it when determining the limit.

CREATE DEFINER=`root`@`localhost` PROCEDURE `consultarPostagens`(
in cat varchar(40),
in limite int(11)
)

BEGIN
    declare v_limite int;

    if limite > 0 or limite <> '' then
        set v_limite = limite;
    else
        set v_limite = 10000;
    end if;

    select * from tb_post where
        (   case when cat is null or cat = '' then categoria is not null
            else categoria = cat end) and
        (   case when limite > 0 or limite <> '' then limit limite end )
    limit v_limite;
END

Reference on variables: https://www.devmedia.com.br/variaveis-com-sql-declare-e-select-into/6181

Browser other questions tagged mysql procedure

You are not signed in. Login or sign up in order to post.