Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
atwork8
Starting Member
3 Posts |
Posted - 2007-08-30 : 15:14:32
|
Hi there,I'm new to T-SQL and i'm trying to implement the efficient paging method outlined in scottgu's article: http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx The problem is i'm trying to make the stored procedure Dynamic, but when I run the following Stored procedure I get the following error:Incorrect syntax near ')'.Invalid object name 'ItemEntries'.It looks like it is to do with the common table expression "ItemEntries". What would be the correct syntax for the following dynamic sql:SET @sqlString = 'With ItemEntries as ( SELECT ROW_NUMBER() OVER (ORDER BY price ASC) as Row, dogID FROM tblDogs WHERE ' + @whereClause + ')'EXEC(@sqlString)Any help would be very much appreciated, thanks |
|
atwork8
Starting Member
3 Posts |
Posted - 2007-08-30 : 19:22:57
|
AT LAST I FIGURED IT OUT!!if anybody else is interested...when executing a common table expression (CTE) you must also include the statement 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 dogIDFROM ItemEntriesWHERE Row between ' + @startRowIndex + ' and ' + @endRowIndex); |
|
|
|
|
|