quote:

---

Originally posted by sql777

wouldn't it be faster by JUST putting the rowNumber and PKID in the CTE, and then doing an inner join on the actual table? (basically keeping the CTE as lean as possible).

Graz?

---

quote:

---

Originally posted by rehle

Here is a twist on the solution provided. This allows me to just write a new view or use a table to get paging results. I have taken this alot further then just the query. I have a few support table and I extended my grid controls, both for windows and the web to include a new property. By setting the custom property the grid gets it entire setup parameters. In the end though I have (1) stored procedure that runs all my grids.

Here is my twist on the query:

CREATE PROCEDURE [dbo].[sp_Grid_Paging_Sorting_Filtering]
(
@pageNum int
,@pageSize int
,@TableName nvarchar(100) --This can be a table or view
,@orderby varchar(max)
,@whereclause varchar(max)
)

AS

declare @lownum nvarchar(10)
declare @highnum nvarchar(10)
declare @sql nvarchar(4000)

set @lownum = convert(nvarchar(10), (@pagesize * (@pagenum - 1)))
set @highnum = convert(nvarchar(10), (@pagesize * @pagenum))

set @sql = 'select * from (
select row_number() over (order by ' + @orderby + ') as rownum
,*
from ' + @TableName + ' ' + @whereclause + '
) A WHERE RowNum > ' + @lownum + ' AND rownum <= ' + @highnum

-- Execute the SQL query
EXEC sp_executesql @sql

---

DECLARE	@PageSize INT,
	@PageNumber INT,
	@FirstRow INT,
	@LastRow INT

SELECT	@PageSize = 20,
	@PageNumber = 3

SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
	@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

WITH Members  AS
(
	SELECT	M_NAME,
	ROW_NUMBER() OVER (ORDER BY M_NAME DESC) AS RowNumber
	FROM	dbo.FORUM_MEMBERS
)
SELECT	RowNumber, fm.M_NAME, M_POSTS, M_LASTPOSTDATE, M_LASTHEREDATE, M_DATE, M_COUNTRY
FROM	Members m inner join FORUM_MEMBERS fm on m.M_NAME = fm.M_NAME
WHERE	RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY RowNumber ASC;