CODEWITHSUNDEEP
mysqlstored-procedures
Pradip
Pradip

Reputation: 1327

MySQL - Stored Procedure limit issue

I have written a stored procedure in SQLYog in MySQL as follows,

DELIMITER $$

DROP PROCEDURE IF EXISTS test.spUser$$

CREATE PROCEDURE test.spUser(IN start1 INT,IN limit1 INT)

BEGIN

select gi.id,gi.user_id,concat(concat(first_name,' '),last_name) as full_name from generic as gi, profile as p where gi.user_id=p.user_id limit start1,limit1;

END$$

DELIMITER ;

If I have written above code then it throws error no. 1064 and if I have replaced limit start1,limit1 by limit 5,10 then it run properly.

How can I resolve this problem?

Upvotes: 0

Views: 1086

Answers (1)

Jon Black
Jon Black

Reputation: 16559

drop procedure if exists list_users;

delimiter #

create procedure list_users()
begin
  set SQL_SELECT_LIMIT = 1;

  select * from users;

  set SQL_SELECT_LIMIT = DEFAULT;
end#

delimiter ;


select * from users;

+---------+----------+
| user_id | username |
+---------+----------+
|       3 | alpha    |
|       2 | bar      |
|       4 | beta     |
|       1 | f00      |
+---------+----------+
4 rows in set (0.00 sec)


call list_users();

+---------+----------+
| user_id | username |
+---------+----------+
|       3 | alpha    |
+---------+----------+
1 row in set (0.00 sec)

Upvotes: 1

Related Questions