Sql2005 stored procedure pagination sample code _mssql

Copy Code code as follows:

--Pagination Stored Procedure example
Alter PROCEDURE [dbo]. [Jh_pagedemo]
@pageSize int = 9000000000,
@pageIndex int = 1,
@orderBy Nvarchar (200) = '--without order by
As
SET NOCOUNT on
--Declaring variables
DECLARE @select VARCHAR (3048);
DECLARE @from VARCHAR (512);
DECLARE @RowNumber VARCHAR (256);
DECLARE @condition NVARCHAR (3990);
DECLARE @groupBy varchar (50);
DECLARE @sql VARCHAR (3998);
DECLARE @RowStartIndex INT;
DECLARE @RowEndIndex INT;
BEGIN
SET NOCOUNT on
IF @orderBy <> '
Set @orderBy = ' ORDER by ' + @orderBy;
Else
Set @orderBy = ' ORDER by Userid ';
SET @select = ' select Userid,username, ';
--Set Sort statements
SET @RowNumber = ' row_number () over (' + @orderBy + ') as RowNumber ';
SET @select = @select + @RowNumber;
SET @from = ' from users ';
--Set Conditional statement @gulevel
SET @condition = ' WHERE 1=1 ';
SET @condition = @condition + ' and userid > 0 ';
--Grouped statements
SET @groupBy = ' GROUP by USerID '
SET @RowStartIndex = (@pageIndex-1) * @pageSize + 1
SET @RowEndIndex = @pageIndex * @pageSize;
--Query results
Set @sql = ' Set NOCOUNT on;
With Resulttable as (' + @select + @from + @condition + ')
SELECT * from resulttable WHERE rownumber between ' +
Cast (@RowStartIndex as VARCHAR) + ' and ' + cast (@RowEndIndex as VARCHAR (32))
+ ' ; SELECT Count (*) as TotalCount ' + @from + @condition + '

--print @sql;
EXEC (@sql);
End