0

I have written a procedure in MySql here:

CREATE DEFINER = 'root'@'localhost'
PROCEDURE bbet.Proc_transactiontable_Get(IN in_userID varchar(36),
IN in_page int,
IN in_amountPerPage int,
IN in_serviceID varchar(36))
BEGIN
SET @offset = ((in_page-1)*in_amountPerPage);
SELECT t.TransactionID, t.UserID, t.ServiceID, t.MatchID, t.AmountMoney, t.CreatedDate, t.Staus, t.TotalMoney, t.Fee FROM transactiontable t
WHERE t.UserID = COALESCE(in_userID, t.UserID) AND t.ServiceID = COALESCE(in_serviceID, t.ServiceID)
LIMIT in_amountPerPage
OFFSET @offset;
END

But it has syntax error on line: "OFFSET @offset". How to handle this problem?

Stu
  • 30,392
  • 6
  • 14
  • 33
Ma Việt Tùng
  • 7
  • 2

1 Answers1

0

I resolved my problem by using declare keyword instead of '@' character:

BEGIN
DECLARE offset int DEFAULT 0;
SET offset = ((in_page-1)*in_amountPerPage);
SELECT t.TransactionID, t.UserID, t.ServiceID, t.MatchID, t.AmountMoney, t.CreatedDate, t.Staus, t.TotalMoney, t.Fee FROM transactiontable t
WHERE t.UserID = COALESCE(in_userID, t.UserID) AND t.ServiceID = COALESCE(in_serviceID, t.ServiceID)
LIMIT in_amountPerPage
OFFSET offset;
END

Dont know why but it worked

Ma Việt Tùng
  • 7
  • 2
  • Read the manual The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions: Within prepared statements, LIMIT parameters can be specified using ? placeholder markers. **Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.** – P.Salmon Dec 10 '22 at 11:47
  • https://dev.mysql.com/doc/refman/8.0/en/select.html – P.Salmon Dec 10 '22 at 11:47
  • https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql – P.Salmon Dec 10 '22 at 11:49