Paging in SQL 2005 - Row_Number()

I am trying to use the new Sql 2005 feature Row_Number, and as a base point,
used one of ScottGu's excellent blogs as a template.
(http://weblogs.asp.net/scottgu/arch.../07/434787.aspx). I modified
it for my purpose and as written below, works fine.,
HOWEVER ...
There are a few fields that I would like to capture that are not on either
table (The Stores table or the Products table). The columns are on the Media
table that us linked to the products talbe by the ProductID
(
SELECT dbo.Media.MediaURL, ImageLevel
FROM dbo.Media INNER JOIN
dbo.Products ON dbo.Media.ProductID =
dbo.Products.ProductID
)
So, in the following SPROC, how can I modify it so that I can include these
new column values? I have tried quite a few combinations, but get errors.
--Here is the SPROC that
works --
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GetProductsByStoreIDPaged]
@.StoreId INT,
@.PageIndex INT,
@.NumRows INT,
@.StoreName nvarchar(50) OUTPUT,
@.StoreProductCount INT OUTPUT
AS
BEGIN
SELECT @.StoreProductCount=(SELECT COUNT(*) FROM Products where
Products.StoreId=@.StoreId)
SELECT @.StoreName=(Select StoreName FROM Stores Where
Stores.StoreID=@.StoreId)
Declare @.startRowIndex INT;
set @.startRowIndex = (@.PageIndex * @.NumRows) + 1;
With ProductEntries as (
--here the selects start ...
SELECT ROW_NUMBER() OVER
(ORDER BY ProductId ASC) as Row,
ProductId,
StoreId, ProductName, ImageURLDisplay
FROM
Products
WHERE StoreId=@.StoreId
)
Select ProductId, StoreId, ProductName,
ImageURLDIsplay
FROM ProductEntries
WHERE Row between
@.startRowIndex and @.StartRowIndex+@.NumRows-1
END
---
Here is the original SPROC without paging (that works fine):
SELECT TOP 100 PERCENT dbo.Stores.StoreID, dbo.Stores.StoreName,
dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.Description,
dbo.Media.MediaURL, dbo.Media.ImageLevel
FROM dbo.Products INNER JOIN
dbo.Stores ON dbo.Products.StoreID = dbo.Stores.StoreID INNER JOIN
dbo.Media ON dbo.Products.ProductID = dbo.Media.ProductID
WHERE (dbo.Media.ImageLevel = 1) AND
(dbo.Stores.StoreID = @.StoreID)
ORDER BY dbo.Media.ImageSortCol, dbo.Products.ProductID DESC
----
I am trying to transform the original SPROC above into a paged SPROC a` la
ScottGu.
Thanks to all.
PaulShould be straightforward, replace your CTE statement with
With ProductEntries as (
SELECT ROW_NUMBER() OVER
(ORDER BY p.ProductId ASC) as Row,
p.ProductId,
p.StoreId, p.ProductName, p.ImageURLDisplay,m.MediaURL
FROM
Products p
INNER JOIN Media m ON m.ProductID=p.ProductID
WHERE p.StoreId=@.StoreId
)
Select ProductId, StoreId, ProductName,
ImageURLDIsplay ,MediaURL
FROM ProductEntries
WHERE Row between
@.startRowIndex and @.StartRowIndex+@.NumRows-1