高效分页存储过程
--分页存储过程示例
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
–分页存储过程示例
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
