高效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

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

巴特西

高效sql2005分页存储过程

最新文章

热门文章