Friday, January 9, 2009

Stored Procedure Paging in My Sql

Sample code of stored procedure for paging in My Sql. The input parameter are DatabaseName, WhereClause, OrderBy, PageIndex, and PageSize. The output parameter is TotalRecords.


DELIMITER $$

CREATE PROCEDURE `GetPaged`(IN DatabaseName varchar(200), IN WhereClause varchar(200),
         IN OrderBy varchar(200), IN PageIndex int, IN PageSize int, OUT TotalRecords int)

BEGIN
    DECLARE _WhereClause VARCHAR(200);
    DECLARE _OrderBy VARCHAR(200);
    DECLARE _Limit VARCHAR(20);

    SET _Limit = ' LIMIT ?,?';
    IF LENGTH(WhereClause) > 0 THEN SET _WhereClause = CONCAT(' WHERE ', WhereClause);
       ELSE SET _WhereClause = '';
    END IF;

    IF LENGTH(OrderBy) > 0 THEN SET _OrderBy = CONCAT(' ORDER BY ', OrderBy);
       ELSE SET _OrderBy = '';
    END IF;

    SET @SQLStatement = CONCAT('SELECT * FROM ',DatabaseName, _WhereClause, _OrderBy, _Limit);
    SET @Count = CONCAT('SELECT COUNT(1) INTO @TotalRecords FROM ',DatabaseName, _WhereClause);

    SET @v_PageIndex = PageIndex;
    SET @v_PageSize = PageSize;

    PREPARE SQLStatement FROM @SQLStatement;
    EXECUTE SQLStatement using @v_PageIndex,@v_PageSize;
    DROP PREPARE SQLStatement;

    PREPARE COUNTStatement FROM @Count;
    EXECUTE COUNTStatement;
    DROP PREPARE COUNTStatement;
    SET TotalRecords = @TotalRecords;
END $$

DELIMITER ;

No comments: