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