ScottGu Efficient Paging problem, dynamic (CTE)

Hi there,

I'm trying to implement the efficient paging methodoutlined in scottgu's article:http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

Theproblem is i'm trying to make the stored procedure Dynamic, but when Irun the following Stored procedure I get the following error:

Incorrect syntax near ')'.
Invalid object name 'ItemEntries'.

I've used the step in facility in Visual Studio and these are the values of the variables:

@.whereClause = tblDogs.dogCode = 'LAB'

@.sqlb= With ItemEntries as (SELECT ROW_NUMBER() OVER (ORDER BY dogID ASC) asRow, dogID FROM tblDogs WHERE tblDogs.dogCode = 'LAB')

Here is the full SP:

ALTER PROCEDURE [dbo].[spPropertyGetPagedPropertiesFromSearch]
@.whereClause varchar(1000),
@.pageIndex INT,
@.numRows INT,
@.itemCount INT OUTPUT
AS

BEGIN
DECLARE @.sqlb nvarchar(1000);

SELECT @.itemCount=(SELECT COUNT(*) FROM tblDogs WHERE tblDogs.dogCode = 'LAB')

/*
The below statements use the new ROW_NUMBER() function in SQL 2005 to return only the specified
rows we want to retrieve from the Products table
*/

Declare @.startRowIndex INT;
set @.startRowIndex = (@.pageIndex * @.numRows) + 1;

SET@.sqlb = 'With ItemEntries as (SELECT ROW_NUMBER() OVER (ORDER BY priceASC) as Row, dogID FROM tblDogs WHERE ' + @.whereClause + ')'
EXEC(@.sqlb)

Select dogID
FROM ItemEntries
WHERE Row between
@.startRowIndex and @.StartRowIndex+@.NumRows-1
END

I've been at this all day and I'm going insane lol can't find an answer anywhere, so any help would be amazing thanks

AT LAST I FIGURED IT OUT!!

if anybody else is interested...

whenexecuting a common table expression (CTE) you must also include thestatement that immediately follows it and uses the CTE e.g.

Declare @.startRowIndex INT;
Declare @.endRowIndex INT;
set @.startRowIndex = (@.pageIndex * @.numRows) + 1;
set @.endRowIndex = (@.StartRowIndex+@.NumRows-1);

EXEC('WITH ItemEntries AS (SELECT ROW_NUMBER() OVER (ORDER BY dogID ASC) AS Row, dogID FROM tblDogs WHERE ' + @.whereClause + ')

Select dogID

FROM ItemEntries

WHERE Row between ' + @.startRowIndex + ' and ' + @.endRowIndex);