Friday, 28 March 2014

Get page wise result data in SQL Server



CREATE PROC usp_GetPagingData
      @CurrentPage INT = NULL,
      @PageSize INT = NULL
AS
BEGIN
      DECLARE @RowsFrom INT
      DECLARE @RowsTo INT
      DECLARE @TotalRecords INT
      DECLARE @NoOfRecs INT
      DECLARE @RecordSize NVARCHAR(MAX)
      DECLARE @TotalPage INT
     
     
      -- Get result data
      SELECT ID, Code, Name_en
      INTO #Result
      FROM TblAreas
     
     

      SET @RowsFrom = @CurrentPage * @PageSize - @PageSize + 1
      SET @RowsTo = @CurrentPage * @PageSize
     

      SELECT @TotalRecords = count(1)
      FROM #Result

      SELECT @NoOfRecs = count(1)
      FROM #Result

      IF (@PageSize != NULL) OR (@PageSize > 0)
      BEGIN
            SET @RowsFrom = @CurrentPage * @PageSize - @PageSize + 1
            SET @RowsTo = @CurrentPage * @PageSize
           
            print @PageSize
            print @RowsFrom
            print @RowsTo          
      END              
      ELSE
      BEGIN
            SET @PageSize = @NoOfRecs          
            SET @RowsFrom = 1
            SET @RowsTo = @NoOfRecs      
           
            print @PageSize
            print @RowsFrom
            print @RowsTo
      END

      SET @RecordSize = CONVERT(VARCHAR, @PageSize)

      IF (@TotalRecords % @RecordSize <> 0)
      BEGIN
            SELECT @TotalPage = CEILING(@TotalRecords / @RecordSize) + 1
      END
      ELSE
      BEGIN
            SELECT @TotalPage = CEILING(@TotalRecords / @RecordSize)
      END

      SELECT @TotalPage TotalPage

      SELECT @TotalRecords TotalRecords;

      WITH Result
      AS (
            SELECT *,
                  ROW_NUMBER() OVER (
                        ORDER BY ID
                        ) AS RowNumber
            FROM #Result
            )
           
      SELECT *
      FROM Result
      WHERE RowNumber >= CONVERT(NVARCHAR(10), isnull(@RowsFrom, 0))
            AND RowNumber <= CONVERT(NVARCHAR(10), isnull(@RowsTo, @NoOfRecs))

      DROP TABLE #Result
END

Go

EXEC usp_GetPagingData 2, 5

Output:


No comments:

Post a Comment