CREATE PROCEDURE GetProductsInCategory
(@CategoryID INT, @DescriptionLength INT, @PageNumber INT, @ProductsPerPage INT, @HowManyProducts INT OUTPUT)
AS

-- declare a new TABLE variable
DECLARE @Products TABLE
(
	RowNumber			INT,
	ProductID			INT,
	Name				VARCHAR(50),
	Description			VARCHAR(5000),
	Price				MONEY,
	Image1FileName			VARCHAR(50),
	Image2FileName			VARCHAR(50),
	OnDepartmentPromotion	bit,
	OnDepartmentPromotion	bit)

-- populate the table variable with the complete list of products
INSERT INTO @Products
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),
		Product.ProductID, Name, 
                SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description,Price,
		Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product INNER JOIN ProductCategory
	ON Product.ProductID = ProductCategory.ProductID
WHERE ProductCategory.CategoryID = @CategoryID

-- return the total number of products using an OUTPUT variable
SELECT @HowManyProducts = COUNT(ProductID) FROM @Products

-- extract the requested page of products
SELECT ProductID, Name, Description, Price, Image1FileName,
		Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM @Products
WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage
	AND RowNumber <= @PageNumber * @ProductsPerPage