MySQL Forums
Forum List  »  Stored Procedures

Re: circumventing limitations on the "Liimit" clause
Posted by: William Chiquito
Date: August 29, 2007 12:29PM

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 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: circumventing limitations on the "Liimit" clause
3627
August 29, 2007 12:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.