IS THEIR ANYTHING LIKE NEXTTOP FUNCTION AFTER USING TOP()

Abraham Olatubosun

7y891

Hi all, Hope this question meet you in good health. I am writing an application that will migrate all our patient record's from our legacy software to a new open source software (OPENERS) migrating say 2000 records take two day approx. So, I want to know if their is anything like NEXT(500) function or script in MSSQL SERVER 2014 to help me select 500 records at a time till I come to the end of the 2000 records. Thanks for any solution

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar7y ago
Hi Abraham,

you need to execute this procedure is like below,

first parameter is page number
second parameter is number of records
exec dbo.TestPaging 1,5
Abraham Olatubosun7y ago
Many thanks Jignesh for your response, i really appreciate i did some reading on OFFSET and FETCH NEXT before trying your example, after implimenting your example this way

exec TestPage((Select count(*) from PersonalHistGen_info))

I got the following error as shown below:

Msg 201, Level 16, State 4, Procedure TestPage, Line 26
Procedure or function 'TestPage' expects parameter '@PageNumber', which was not supplied.

(1 row(s) affected)

What should I do next, because if you look at the EXEC section above I actually provide for @PageNumber the total rows in the database is 10601.

I appreciate any advice.
Jignesh Kumar7y ago
Hi Abraham,

SQL server 2012 onwords, We have OFFSET and FETCH NEXT option to do this.
1.  CREATE PROCEDURE TestPaging
2.  (
3.    @PageNumber INT,
4.    @PageSize INT
5.  )
6.  AS
7.  DECLARE @OffsetCount INT
8.  SET @OffsetCount = (@PageNumber-1)*@PageSize
9.  SELECT  *
10. FROM [UserDetail]
11. ORDER BY [User_Id]
12. OFFSET @OffsetCount ROWS
13. FETCH NEXT @PageSize ROWS ONLY