Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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 

Open in new window

0
tbaseflug
Asked:
tbaseflug
1 Solution
 
HainKurtSr. System AnalystCommented:
dont use temp file, keep it simple just use following code

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 (
SELECT *, ROW_NUMBER() OVER(ORDER BY hcpcs DESC) AS 'RowNumber'
FROM dbMasterdata.dbo.tblADDB
) x where rn between (@StartIndex + 1) AND (@StartIndex + @PageSize)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now