sql2005 存储过程分页示例代码

--分页存储过程示例
Alter PROCEDURE [dbo].[JH_PageDemo]
@pageSize int = 9000000000,
@pageIndex int = 1 ,
@orderBy Nvarchar(200) = -- 不加order By
AS
SET NOCOUNT ON
--声明变量
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 @RowNumber =ROW_NUMBER() OVER ( + @orderBy + ) as RowNumber ;
SET @select = @select + @RowNumber;
SET @from = FROM users ;
--设置条件语句@GULevel
SET @condition = WHERE 1=1 ;
SET @condition = @condition + AND userid > 0;
--分组语句
SET @groupBy = GROUP BY USerID
SET @RowStartIndex = ( @pageIndex -1) * @pageSize + 1
SET @RowEndIndex = @pageIndex * @pageSize ;
--查询结果
SET @sql = SET NOCOUNT ON;
WITH ResultTable AS ( + @select + @from + @condition +)
SELECT * FROM ResultTable WHERE RowNumber between +
Cast(@RowStartIndex AS VARCHAR(32)) + AND + CAST(@RowEndIndex AS VARCHAR(32))
+ ; SELECT count(*) as totalcount + @from + @condition +

--PRINT @sql;
EXEC(@sql);
END