复制代码 代码如下: --分页存储过程示例 Alter PROCEDURE [dbo].[JH_PageDemo] @pageSize int = 9000000000, @pageIndex int = 1 , @orderBy Nvarchar(200) = '' -- 不加order By AS SET NOCOUNT ON --声明变量 DECLARE @select VARCHA
复制代码 代码如下:--分页存储过程示例 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
