MySql Stored Procedures

swdev · February 17th, 2009, 04:30 PM

Hi,

I'm currently reading this book using MySQL instead of SQL Server. I'm trying to get the stored procedures converted to MySQL stored procedures. I think I've managed to get all but two of them converted. Is there anyone who can help me with the MySQL equivalent to the following Stored Procedures:

Code:

 
CREATE PROCEDURE dbo.tbh_Articles_GetArticles
(
  @PageIndex  int,
  @PageSize   int
)
AS
SET NOCOUNT ON
SELECT * FROM
(
  SELECT tbh_Articles.ArticleID, tbh_Articles.AddedDate, tbh_Articles.AddedBy,
     tbh_Articles.CategoryID, tbh_Articles.Title, tbh_Articles.Abstract,
     tbh_Articles.Body, tbh_Articles.Country, tbh_Articles.State,
     tbh_Articles.City, tbh_Articles.ReleaseDate, tbh_Articles.ExpireDate,
     tbh_Articles.Approved, tbh_Articles.Listed, tbh_Articles.CommentsEnabled,
     tbh_Articles.OnlyForMembers, tbh_Articles.ViewCount, tbh_Articles.Votes,
     tbh_Articles.TotalRating, tbh_Categories.Title AS CategoryTitle,
     ROW_NUMBER() OVER (ORDER BY ReleaseDate DESC) AS RowNum
     FROM tbh_Articles INNER JOIN
        tbh_Categories ON tbh_Articles.CategoryID = tbh_Categories.CategoryID
) Articles
  WHERE Articles.RowNum BETWEEN
     (@PageIndex*@PageSize+1) AND ((@PageIndex+1)*@PageSize)
  ORDER BY ReleaseDate DESC
 
 
----------------------------------------------------------------------------------------------------------------------------
 
 
CREATE PROCEDURE dbo.tbh_Articles_GetPublishedArticlesByCategory
(
  @CategoryID    int,
  @CurrentDate   datetime,
  @PageIndex     int,
  @PageSize      int
)
AS
SET NOCOUNT ON
SELECT * FROM
(
  SELECT tbh_Articles.ArticleID, tbh_Articles.AddedDate, tbh_Articles.AddedBy,
     tbh_Articles.CategoryID, tbh_Articles.Title, tbh_Articles.Abstract,
     tbh_Articles.Body, tbh_Articles.Country, tbh_Articles.State,
     tbh_Articles.City, tbh_Articles.ReleaseDate, tbh_Articles.ExpireDate,
     tbh_Articles.Approved, tbh_Articles.Listed, tbh_Articles.CommentsEnabled,
     tbh_Articles.OnlyForMembers, tbh_Articles.ViewCount, tbh_Articles.Votes,
     tbh_Articles.TotalRating, tbh_Categories.Title AS CategoryTitle,
     ROW_NUMBER() OVER (ORDER BY ReleaseDate DESC) AS RowNum
     FROM tbh_Articles INNER JOIN
        tbh_Categories ON tbh_Articles.CategoryID = tbh_Categories.CategoryID
     WHERE Approved = 1 AND Listed = 1 AND
        ReleaseDate <= @CurrentDate AND ExpireDate > @CurrentDate
        AND tbh_Articles.CategoryID = @CategoryID
) Articles
  WHERE Articles.RowNum BETWEEN
     (@PageIndex*@PageSize+1) AND ((@PageIndex+1)*@PageSize)
  ORDER BY ReleaseDate DESC

Thanks in advance

Old Pedant · February 17th, 2009, 10:16 PM

MySQL provides a *MUCH* better mechanism for paging than does SQL Server. (And, just incidentally, that's a pretty crappy way to do paging in SQL Server...the performance will stink as the number of records in the paging set gets huged...but never mind.)

MySQL has this wonderful keyword called LIMIT.

http://dev.mysql.com/doc/refman/5.1/en/select.html
and look for LIMIT.

Fundamentally, if you are going to pass in the two arguments PageIndex (or PageNumber) and PageSize, all you have do do is contruct the LIMIT accordingly. Thus:

Code:

SELECT ... FROM ... WHERE ... ORDER BY ...
LIMIT (PageNumber-1)*PageSize, PageSize

That assumes your PageNumber values start at 1. If they start at zero, you of course don't need to subtract 1 before multiplying.

Isn't that outstandingly simple??

If you managed to convert all the other SP's then I suspect that the above is all the help you need. But if not, please post again.