How to use 'limit' within a MYSQL procedure

Question

How to use 'limit' within a MYSQL procedure

Navigation

#1 by (1 votes)

0

Are you talking about beauty?

Next, I'm setting up a procedure, where I enter as a parameter the number of rows it will return and if it is 0 or it is blank, it returns all possible rows, however in my logic it is giving syntax error and then needed of your opinion. Here's 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, the procedure returns all posts and if a category is defined, it brings up all posts in that category.

The problem is at the limit, I do not know why I can not put the term ' limit ' in it, it's the syntax error.

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

Valeus!

mysql procedure

asked by anonymous 15.05.2018 / 22:54

1 answer

How to make the background image for each day different? How to load an Error 404 page for the site and another for HTACCES application?

score 1 · Accepted Answer

I have 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 a variable and then I 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: link