有效的分页存储过程

CREATE

 

 

PROCEDURE [dbo].[Profile_GET]

@PageSize

 

int=null,

    @CurrentPage

 

int=null,

    @SortExpression   

 

nvarchar(max)=null

AS

BEGIN

   

 

SETNOCOUNTON

   

 

DECLARE @SqlString nvarchar(max)

   

 

Declare @UpperBand int

   

 

Declare @LowerBand int       

   

   

 

SET @LowerBand  =(@CurrentPage - 1)* @PageSize

   

 

SET @UpperBand  =(@CurrentPage * @PageSize)+ 1   

   

 

BEGIN

       

 

SET @SqlString='WITH tempProfile AS

        (                   

            SELECT

                [ProfileId],

                [Name],

                [Address],

                [Email],

                [Mobile],

                [Active] = CASE [IsActive] WHEN 1

                THEN ''Active'' WHEN 0 THEN ''DeActive'' END,                           

                ROW_NUMBER() OVER (ORDER BY '

 

 

+ @SortExpression +' ) AS RowNumber                

                FROM [dbo].[Profile]

        )    

        SELECT

            [ProfileId],

            [Name],

            [Address],

            [Email],

            [Mobile],

            [Active]                                       

        FROM

            tempProfile

        WHERE

            RowNumber > '

 

 

+CONVERT(VARCHAR,@LowerBand)+' AND RowNumber < '+CONVERT(VARCHAR, @UpperBand)

           

 

+' ORDER BY '+ @SortExpression  ;         

   

   

 

EXECsp_executesql@SqlString

   

 

END

END

 

 

;

CREATE

 

 

PROCEDURE [dbo].[Profile_Total]

AS

BEGIN

   

 

SETNOCOUNTON

   

 

SELECTCOUNT(*)FROMProfile

END