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);
