HI all,
I am very new to SQL server and have been working my way through a book to learn ASP.Net v2 and SQL by building a e-commerce site.
There are 3 stored procedures which are used to implement paging from retrieving products from the catalog, which are either on a catalog promotion or a department promotion.
The problem is is that the stored procedure is written in sql 2005 and doesn't work on sql 2000.
I have been trying to change this to sql 2000 but don't have enough experience of sql to do this. can anyone help me with this.
If someone can help me with one of the procedures then i think i should be able to fix the other two.
One of the procedures look like this:
CREATE PROCEDURE GetProductsOnCatalogPromotion
(@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,
OnCatalogPromotion BIT)
-- populate the table variable with the complete list of products
INSERT INTO @Products
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),
ProductID, Name,
SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description,
Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product
WHERE OnCatalogPromotion = 1
-- 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 * @ProductsPerPageI would really appreciate anybody help that can help me with this. i have really tried but i can't get this right
Thanks in advance