IS THEIR ANYTHING LIKE NEXTTOP FUNCTION AFTER USING TOP()

Question

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

Jignesh Kumar · Answer

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 Olatubosun · Answer

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 Kumar · Answer

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