Details

Published: Friday, 17 April 2015 13:14

Hits: 2502

Using the LIMIT Clause within a MySQL Stored Procedure like this...

delimiter //
CREATE PROCEDURE MyProc(IN NbrRows INT)
BEGIN
  SELECT * FROM MYTABLE LIMIT NbrRows;
END;
//
delimiter ;

...will not work since the LIMIT Clause expects a constant value, such as 10, like this...

delimiter //
CREATE PROCEDURE MyProc(IN NbrRows INT)
BEGIN
  SELECT * FROM MYTABLE LIMIT 10;
END;
//
delimiter ;

One way around this is to use Dynamic SQL to prepare a SQL statement using a string and then execute the text within the string. For example...

delimiter //
CREATE PROCEDURE MyProc(IN NbrRows INT)
BEGIN
  SET @sSQL = "SELECT * FROM MYTABLE LIMIT ?";
  SET @iNbrRows = NbrRows;
  PREPARE STMT FROM @sSQL;
  EXECUTE STMT USING @iNbrRows;
END;
//
delimiter ;

Note that I have seen suggestions to use the SQL_SELECT_LIMIT option to limit the number of rows coming out of a SQL SELECT Query. This will not work within a Stored Procedure, but it is rumored to work within a Stored Function. With the code above, you can ignore the SQL_SELECT_LIMIT option.