Need syntax help

[Jcarr38]

Hello, i am more of a MS Sql guy but need help on a stored procedure. If anyone could please help me with the main sql i would greatly appreciate the help, i'm still sorta confused after looking at the documentation for MYSQL. Here is the procedure. I know ROW_NUMBER isnt for Mysql but i need this procedure to function on the three different pieces. I'm thinking that i could just add in a new column in the database called RowNumber and use that and just order by productid. Thank you very much.


DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`GetProductsInCategory`$$

CREATE PROCEDURE `test`.`GetProductsInCategory`
(in_CategoryID int,
in_DescriptionLength int,
in_PageNumber int,
in_ProductsPerPage int,
out_HowManyProducts int out)

BEGIN

DECLARE in_Products1 TABLE
(RowNumber int,
ProductID int,
ProductName varchar(50),
ProductDescr varchar(5000),
Price decimal,
imageThumb varchar(50),
imageBig varchar(50),
onFrontPage boolean)

#(1)populate the table variable with the complete list of products

INSERT INTO in_Products1
SELECT ROW_NUMBER() OVER (ORDER BY products.ProductID),
Products.ProductID, productName,
SUBSTRING(ProductDescr, 1, in_DescriptionLength) + '...' As ProductDescr, Price,
ImageThumb, ImageBig, onFrontPage
FROM products INNER JOIN ProductCategory
ON products.ProductID = ProductCategory = in_CategoryID

#(2)return the total number of products using an output variable
SELECT out_HowManyProducts = COUNT(ProductID) FROM in_Products1

#(3)extract the requested page of products
SELECT ProductID, ProductName, ProductDescr, price, ImageThumb, ImageBig, onFrontPage
FROM in_Products1
WHERE RowNumber > (in_PageNumber - 1) * in_ProductsPerPage
AND RowNumber <= in_PageNumber * in_ProductsPerPage


END$$

DELIMITER ;