SQL SERVER PAGING OUTPUT DATA TO GRIDVIEW SAMPLE CODE

when you create gridview display multiple rows so you can divide rows query for sql server according your paging selection this will demanded to pass page size and page index to get query result .
this is best for performance to get from database rows needed to show not load all rows every postback for gridview paging.

sql server sample  code:

BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
---------------------------------------------------------------
DECLARE @INTPAGE INT =2 ;
DECLARE @INTPAGESIZE INT =10 ;
DECLARE @REQUEST_REPORT_NO VARCHAR(50) = NULL
   DECLARE @PATIENT_PROFILE_NO VARCHAR(50) = NULL
    DECLARE @REQUEST_STAGE_ID VARCHAR(50) = NULL
    DECLARE @REQUEST_STATUS_ID VARCHAR(50) = NULL
    DECLARE @FROM_DATE DATETIME = NULL
    DECLARE @TO_DATE DATETIME = NULL
   
DECLARE @INTSTARTROW INT;
DECLARE @INTENDROW INT;

SET @INTSTARTROW = (@INTPAGE -1) * @INTPAGESIZE + 1;
SET @INTENDROW = @INTPAGE * @INTPAGESIZE;  
--------------------------------------------------------------

WITH BLOGS AS
(
SELECT VR.*  ,
 ROW_NUMBER() OVER(ORDER BY VR.CREATE_DATE DESC) AS INTROW ,
      COUNT(VR.REQUEST_REPORT_NO) OVER() AS TOTAL_ROWS_COUNT
FROM VACATION_REPORTS_REQUEST VR
RIGHT JOIN WORK_FLOWS_TYPES_STAGES T ON VR.REQUEST_STAGE_ORDER = T.STAGE_ORDER
WHERE
        T.TYPE_ID = [DBO].[WORK_FLOWS_TYPE_GET_BY_USER_ID](6,VR.CREATE_BY_USER_ID)
  AND T.STAGE_ORDER =VR.REQUEST_STAGE_ORDER
  AND ([REQUEST_REPORT_NO] = @REQUEST_REPORT_NO OR @REQUEST_REPORT_NO IS NULL)
  AND  (@PATIENT_PROFILE_NO IS NULL OR VR.PATIENT_PROFILE_NO = @PATIENT_PROFILE_NO)
  AND  (@REQUEST_STAGE_ID IS NULL  OR T.STAGE_ID = @REQUEST_STAGE_ID)
  AND  (@REQUEST_STATUS_ID IS NULL OR VR.REQUEST_STATUS_ID = @REQUEST_STATUS_ID)
  AND (@FROM_DATE IS NULL OR VR.CREATE_DATE > = @FROM_DATE)
  AND (@TO_DATE IS NULL OR VR.CREATE_DATE < = @TO_DATE)
  AND (@FROM_DATE IS NULL OR
(@TO_DATE IS NOT NULL AND @FROM_DATE IS NOT  NULL AND  VR.CREATE_DATE BETWEEN @FROM_DATE AND @TO_DATE)
)
 
  )
 SELECT * FROM BLOGS
WHERE (@INTPAGESIZE =0 OR INTROW BETWEEN @INTSTARTROW AND @INTENDROW)
END