VirusDotNET

CREATE PROCEDURE GetProductsInCategory(@CategoryID INT, @DescriptionLength INT, @PageNumber INT, @ProductsPerPage INT, @HowManyProducts INT OUTPUT)AS-- declare a new TABLE variableDECLARE @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 productsINSERT INTO @ProductsSELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),		Product.ProductID, Name,                 SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description,Price,		Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotionFROM Product INNER JOIN ProductCategory	ON Product.ProductID = ProductCategory.ProductIDWHERE ProductCategory.CategoryID = @CategoryID-- return the total number of products using an OUTPUT variableSELECT @HowManyProducts = COUNT(ProductID) FROM @Products-- extract the requested page of productsSELECT ProductID, Name, Description, Price, Image1FileName,		Image2FileName, OnDepartmentPromotion, OnCatalogPromotionFROM @ProductsWHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage	AND RowNumber <= @PageNumber * @ProductsPerPage