Apply paging in procedure


@intPageId int,
@intPageSize int




select * from (
Select     row_number() over (order by  TableA.intID asc ) r,
    TableA.intID,tableA.hName,TableB.Address
 from TableA
  Join TableB on TableA.intid = TableB.intId

  ) A
  where A.r  between ( (@intpageid-1)*@intpagesize)+1 and ((@intpageid-1)*@intpagesize)+@intpagesize



The query in bold text is for paging, and the normal text query is that which returns your records.

OR

SELECT * from PressRelease 
order by CreatedDate desc
OFFSET (@PageID-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY

Comments

  1. Muhammad Jawad11 January 2016 at 03:53

    DECLARE @pageID INT =2
    DECLARE @pagesize INT=10

    SELECT MarketId,MarketName
    FROM dbo.Market
    ORDER BY MarketId
    OFFSET (@pageID-1)*@pagesize ROWS
    FETCH NEXT @pagesize ROWS ONLY

    Offset skips number of rows from top that you mention and
    FETCH NEXT pull number of rows that you mention

    ReplyDelete

Post a Comment

Popular posts from this blog

TSQL To Get All the Names of tables and Databases whose data have changed in last 24 Hours

-- ============================================= -- Author: Muhammad Jawad -- Create date: 2/2/2017 -- Description: To Get All the Names of tables and Databases whose data have changed in last 24 Hours -- ============================================= ALTER PROC UP_FindAllDatabaseHaveChanges AS     BEGIN         DECLARE @loopStart INT= 1;         DECLARE @looend INT;         IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL             BEGIN                 DROP TABLE #AllDatabaseTables;             END;         CREATE TABLE #AllDatabaseTables             (               ID INT IDENTITY(1, 1)                      PRIMARY KEY ,       ...
Read more

To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases

-- ============================================= -- Author: Muhammad Jawad -- Create date: 2/3/2017 -- Description: To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases -- ============================================= Alter PROC UP_FindAndBackupAllDatabaseHaveChanges AS     BEGIN ----**************************START: To get all the tables and database names whose data have changed in last 24 hours **************************----         DECLARE @loopStart INT= 1;         DECLARE @looend INT;         IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL             BEGIN                 DROP TABLE #AllDatabaseTables;             END;         CREATE TABLE #AllDatabaseTables             ( ...
Read more