-4

I want to create stored procedure with pagination along with top 100 against a subset of a table. For example in a table(ex:employee table) with more than 3,000,000 records, I want to take the top 100,000 records and do the pagination. I'm able to do the pagination using below script, but I want to take the top 100,000 records and do the pagination.

DECLARE @currentPageNo int,@takeData int
SET @currentPageNo =1
SET @takeData = 10

SELECT DISTINCT emp.empid,emp.name,s.Salary,
FROM Employee emp 
LEFT OUTER JOIN salary S ON emp.empid=S.empid
where emp.empid=12
ORDER BY emp.empid desc
OFFSET (@currentPageNo - 1) *   @takeData  ROWS
 FETCH NEXT   @takeData  ROWS ONLY

I need some suggestions on how this can be achieved. I'm using SQL Server 2012.

James Z
  • 12,209
  • 10
  • 24
  • 44
Ravi
  • 1
  • 4
  • 1
    Not everyone understands the Indian(?) numbering system so you should edit your question and replace `lack` and `crore` with either proper English terms or numbers. – jpw Nov 09 '15 at 15:14
  • 3
    Possible duplicate of [What is the best way to paginate results in SQL Server](http://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server) – Tab Alleman Nov 09 '15 at 15:15
  • This question does not make a lot of sense. What is top lack? – Julien Vavasseur Nov 09 '15 at 16:07
  • @JulienVavasseur it is lakh or lak. It is part of the Indian numbering system. https://en.wikipedia.org/wiki/Lakh – Sean Lange Nov 09 '15 at 16:22
  • I first try to read and understand the question as it has been politely replied to Tab Alleman. Jpw suggested to edit the question with proper explanation or terms understandable by everyone. Nothing has been done. As it is, what this means is not obvious and should be properly explained in the question. – Julien Vavasseur Nov 09 '15 at 16:52

1 Answers1

0

In order to perform pagination properly you will need to ensure the order of the result set and assign row numbers, for this you can use ROWNUMBER() OVER()

You will also need to know the total count of the entire result set. This will tell you how many total pages you have. You can do this in a secondary query or add another column for Total Rows using COUNT() OVER()

Here's an example using your variables:

DECLARE @currentPageNo int,@takeData int
SET @currentPageNo =0
SET @takeData = 10  

  SELECT TOP(@currentPageNo) * FROM
    (
                SELECT  ROW_NUMBER() OVER(ORDER BY [ColumnName]) RowNumber, *
                FROM (

                    SELECT DISTINCT emp.empid,emp.name,s.Salary,COUNT(emp.empid) OVER() TotalRows
                    FROM Employee emp 
                    LEFT OUTER JOIN salary S ON emp.empid=S.empid
                    where emp.empid=12
                ) Q1        
    ) Q2
    WHERE ( @takeData<>0 AND RowNumber BETWEEN ((@currentPageNo * @takeData) + 1) AND ((@currentPageNo +1) * @takeData))
StuckOverflow
  • 991
  • 2
  • 10
  • 21