tbaseflug
asked on
Simple Paging help?
I am trying to setup simple paging - which works great - but if I limit the intial select to say, SELECT TOP 50, *, etc. - The below will not work as the rowNumber is out - do I need to somehow re-seed the row number or...?
DECLARE @startIndex int,
@pageSize int ,
@totalCount int
SET @startIndex = NULL
SET @pageSize = NULL
DROP TABLE #summary
SELECT *, ROW_NUMBER() OVER(ORDER BY hcpcs DESC) AS 'RowNumber' INTO #summary FROM dbMasterdata.dbo.tblADDB
ORDER BY hcpcs
UPDATE #summary
SET @totalCount = (SELECT COUNT(*) FROM #summary)
PRINT @totalCount
SET @startIndex = @pageSize * (@startIndex - 1)
SET @pageSize = CASE WHEN @pageSize IS NULL THEN @totalCount ELSE @pageSize END
SET @StartIndex = CASE WHEN @StartIndex IS NULL THEN 0 ELSE @StartIndex END
SELECT * FROM #summary
WHERE RowNumber BETWEEN (@StartIndex + 1) AND (@StartIndex + @PageSize)
ORDER BY RowNumber
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.