SQL SERVER : Dynamic Paging , Sorting

Example 1
GO
/******
-- Date                Name            Description
-- 04/27/2012        Vijay Mishra    Created



 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[SP_PERMISSION_SEARCH]
@PermissionID bigint=null,
@PermissionName nvarchar(500)=null,
@PermissionDescription nvarchar(max)=null,
@PageNumber int=0,
@Pagesize int=0,
@SortColumn nvarchar(50)=null,
@SortOrder nvarchar(10)=null,
@RowCount int Output

AS
BEGIN         
      
   DECLARE @FirstRecord int, @LastRecord int
   SELECT @FirstRecord = ((@PageNumber - 1) * @Pagesize) + 1
   SELECT @LastRecord = (@PageNumber * @Pagesize )
            
   -- Set the ROWCOUNT to the @FirstRecord from where data read starts
   SET ROWCOUNT @FirstRecord
   -- Get the first row from Table (based on above statement it would retrive only one row)
   DECLARE @FirstRow int
   SELECT @FirstRow = PermissionID FROM [PEGASUS_TBL_PERMISSION] ORDER BY PermissionID asc  
  
   -- Now, set the row count to @Pagesize(maximum rows) and get
    -- all records >= @FirstRow
   SET ROWCOUNT @Pagesize
   -- execute the required condition  
   select * from [PEGASUS_TBL_PERMISSION]
   WHERE (@PermissionName IS NULL OR PermissionName LIKE '%' + @PermissionName + '%')      
      AND (@PermissionDescription IS NULL OR PermissionDescription LIKE '%' + @PermissionDescription + '%')
      AND (@PermissionID IS NULL OR PermissionID = @PermissionID)
    and PermissionID >= @FirstRow
    order by
          CASE WHEN @SortColumn='PermissionID' AND @SortOrder='DESC' THEN PermissionID END DESC,
        CASE WHEN @SortColumn='PermissionID' AND @SortOrder='ASC' THEN PermissionID END ASC,
        CASE WHEN @SortColumn='PermissionName' AND @SortOrder='DESC' THEN PermissionName END DESC,
        CASE WHEN @SortColumn='PermissionName' AND @SortOrder='ASC' THEN PermissionName END ASC,     
        CASE WHEN @SortColumn='PermissionDescription' AND @SortOrder='DESC' THEN PermissionName END DESC,
        CASE WHEN @SortColumn='PermissionDescription' AND @SortOrder='ASC' THEN PermissionName END ASC     
    -- Set total resulted ROWCOUNT
    set @RowCount=@@ROWCOUNT  
    SET ROWCOUNT 0
   
     
END

Example 2


GO
/****** Object:  StoredProcedure [dbo].[SP_PERMISSION_SEARCH_VIJAY]    Script Date: 04/27/2012 15:09:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[PEGASUS_SP_PERMISSION_SEARCH_VIJAY1]
@PermissionID bigint=null,
@PermissionName nvarchar(500)=null,
@PermissionDescription nvarchar(max)=null,
@PageNumber int=0,
@Pagesize int=0,
@SortColumn nvarchar(50)=null,
@SortOrder nvarchar(10)=null,
@RowCount int Output

AS
BEGIN         
          --
           DECLARE @FirstRecord int, @LastRecord int
           SELECT @FirstRecord = ((@PageNumber - 1) * @Pagesize) + 1
           SELECT @LastRecord = (@PageNumber * @Pagesize )
           --Get Total rowcount          
           set @RowCount= (select COUNT(0) from [PEGASUS_TBL_PERMISSION])               
       -- Prepare Common Table               
      ;With PERMISSION AS(SELECT PermissionID,PermissionName,PermissionDescription, ROW_NUMBER() OVER
       (order by
          CASE WHEN @SortColumn='PermissionID' AND @SortOrder='DESC' THEN PermissionID END DESC,
        CASE WHEN @SortColumn='PermissionID' AND @SortOrder='ASC' THEN PermissionID END ASC,
        CASE WHEN @SortColumn='PermissionName' AND @SortOrder='DESC' THEN PermissionName END DESC,
        CASE WHEN @SortColumn='PermissionName' AND @SortOrder='ASC' THEN PermissionName END ASC     
       ) as RowNumber, ROW_NUMBER() OVER(ORDER BY PermissionID ASC) AS Total FROM [PEGASUS_TBL_PERMISSION]
      WHERE (@PermissionName IS NULL OR PermissionName LIKE '%' + @PermissionName + '%')      
      AND (@PermissionDescription IS NULL OR PermissionDescription LIKE '%' + @PermissionDescription + '%')
      AND (@PermissionID IS NULL OR PermissionID = @PermissionID)
      )
      , PermissionCount  as
      (
      SELECT COUNT(0) CNT FROM PERMISSION
     
      )       
   
   --   --Select required colums
      select PermissionID,PermissionName,PermissionDescription,Total+RowNumber -1 as Totalrows
      from PERMISSION
      Where RowNumber >=  case when @FirstRecord = 1 then RowNumber else @FirstRecord end and
            RowNumber <=   case when @LastRecord = 0 then  RowNumber else @LastRecord end
     
  
   
     
END