Re: circumventing limitations on the "Liimit" clause

[William Chiquito]

Hi Jo,

Try:

DELIMITER $$

DROP PROCEDURE IF EXISTS `build_user_UsersByEmail`$$

CREATE DEFINER = CURRENT_USER PROCEDURE `build_user_UsersByEmail`(IN `inApplicationName` VARCHAR(100), IN `inEmail` VARCHAR(100), IN `startIndex` INTEGER, IN `pageSize` INTEGER)
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN
	set @offset = startIndex, @size = pageSize;
	prepare UsersByEmail from
	'SELECT PKID, Username, Email, PasswordQuestion,
	`Comment`, IsApproved, IsLockedOut, CreationDate, LastLoginDate,
	LastActivityDate, LastPasswordChangedDate, LastLockedOutDate
	FROM Users
	WHERE Email LIKE inEmail AND ApplicationName = inApplicationName
	ORDER BY Username Asc LIMIT ?,?';
	execute UsersByEmail using @offset, @size;
	deallocate prepare UsersByEmail;
END$$

DELIMITER ;