Solved

Simple Paging help?

Posted on 2012-03-20
1
267 Views
Last Modified: 2012-03-20
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
Comment
Question by:tbaseflug
1 Comment
 
LVL 51

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 37744121
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question