ROW_NUMBER() function is not recognized in store procedure.

Hello I am Prasad , I have written one store procedure as below. But It gives error message ROW_NUMBER() function is not recognized. what's the fault or what should i change.

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
SELECTROW_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
WHERERowNumber > (@.PageNumber - 1) * @.ProductsPerPage
ANDRowNumber <= @.PageNumber * @.ProductsPerPage

What version of SQL Server is this running on? ROW_NUMBER() was introduced in 2005, so any version before that won't have it.

Don

|||

Hello,I create database in sql2000 but when i got this error. I connect it with sql2005 (same database).and tried to run this store proc. but same error occurs in sql2005. I dint create database in sql2005 only open in sql2005 and tried.

I want to fetch records for paging purpose by its row numbers. so i tried this store proc. is there any other way to fetch without row numbers?

please reply.

|||

prasad bhanage:

Hello,I create database in sql2000 but when i got this error. I connect it with sql2005 (same database).and tried to run this store proc. but same error occurs in sql2005. I dint create database in sql2005 only open in sql2005 and tried.

Row_number() is a T-SQL enhancement that has been introduced in SQL 2005 only. If your database is in SQL 2000 you can't use any of the features introduced after SQL 2000 even if you run them using SQL 2005 tools. visithttp://msdn2.microsoft.com/en-us/library/ms186734.aspx for more information.

Now, to solve you problem as you've SQL 2000, there is one way of using temporary table for this. Your modified procedure is as below. Please make the logical changes as you wish.

CREATE PROCEDURE GetProductsOnCatalogPromotion(@.DescriptionLengthINT,@.PageNumberINT,@.ProductsPerPageINT,@.HowManyProductsINT OUTPUT)ASSELECT identity (bigint , 1 , 1 )as RowNumber , ProductID ,Name ,SUBSTRING(Description , 1 , @.DescriptionLength ) +'...'AS Description , Price , Image1FileName , Image2FileName , OnDepartmentPromotion , OnCatalogPromotioninto #ProductsFROM ProductWHERE OnCatalogPromotion = 1order by Product.ProductIDSELECT @.HowManyProducts =COUNT ( ProductID )FROM #ProductsSELECT ProductID ,Name ,Description , Price , Image1FileName ,Image2FileName , OnDepartmentPromotion , OnCatalogPromotionFROM #ProductsWHERE RowNumber > ( @.PageNumber - 1 ) * @.ProductsPerPageAND RowNumber <= @.PageNumber * @.ProductsPerPage

Hope this will help.

|||

Hi,

You need to create the database in sqlserver 2005.

Then only it will work other wise u can't use perticular keyword in Sql Server 2000.

It was newly introduced in sqlserver 2005 Only.

_____________________________________________________________

Mark as Answer If u find a solution.