Tuesday, 11 February 2014

implement Pagination using store procedure in sql server

Implement pagination in store procedure in sql server.We need to pass Pagesize,PageIndex,sortValue,sortOrder as a input parameters the store procedure will give result based on input values

ALTER PROCEDURE [dbo].[proc_Name]
(
     @AdId            VARCHAR(1000)
    ,@PageIndex        INT    = 0
    ,@PageSize        INT    = 10  
    ,@SortByField    NVARCHAR(MAX) = 'Sorted Column Name'
    ,@SortOrder        NVARCHAR(5) = 'DESC'
)
  
AS
BEGIN  
    SET NOCOUNT ON;

    IF (ISNULL(@PageIndex, '') = '') SELECT @PageIndex = 0
    IF (ISNULL(@PageSize, '') = '') SELECT @PageSize = 10
    IF (ISNULL(@SortByField, '') = '') SELECT @SortByField = 'Sorted Column Name'
    IF (ISNULL(@SortOrder, '') = '') SELECT @SortOrder = 'DESC'

    DECLARE @OffsetCount INT

    SET @OffsetCount = @PageIndex * @PageSize
  
  
    IF OBJECT_ID('tempdb..#tmpResult') IS NOT NULL  DROP TABLE #tmpResult
    SELECT   Columns Name
            , Columns Name
          
    INTO    #tmpResult
    FROM [dbo].[TableName] AP WITH(NOLOCK)   
    INNER JOIN dbo.TableName FB WITH(NOLOCK)
    ON FB.ColumnName=AP.ColumnName
          
    IF OBJECT_ID('tempdb..#tmpData') IS NOT NULL  DROP TABLE #tmpData
  
    DECLARE @MySQL NVARCHAR(MAX)
    SELECT @MySQL = N'
    SELECT * INTO #tmpData FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY [' + @SortByField + '] ORDER BY [' + @SortByField + '] ' + @SortOrder +
        ') AS SlNo FROM #tmpResult) S WHERE SlNo = 1'  
  
    EXEC(@MySQL)

    DECLARE    @TotalItems    INT
    SELECT    @TotalItems = COUNT(AppID) FROM #tmpResult
  
    SELECT @MySQL = N'
    SELECT *, ' + CONVERT(NVARCHAR(MAX), @TotalItems) + '''TotalItems'' FROM #tmpResult ORDER BY [' + @SortByField + '] ' + @SortOrder +
     ' OFFSET ' + CONVERT(NVARCHAR(MAX), @OffsetCount) + ' ROWS FETCH NEXT ' + CONVERT(NVARCHAR(MAX), @PageSize) + ' ROWS ONLY'

    EXEC(@MySQL)

    IF OBJECT_ID('tempdb..#tmpResult') IS NOT NULL  DROP TABLE #tmpResult
    IF OBJECT_ID('tempdb..#tmpData') IS NOT NULL  DROP TABLE #tmpData
                      
END