高效分页存储过程

Technorati 标签: sql

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

  --高效分页存储过程

--Create by Jinlong Zhang

ALTER PROCEDURE [dbo].[GetListByPage]( 

@Table varchar(500),          --表名 

@Field varchar(500) = '*',        --读取字段 

@Where  varchar(500) = NULL,       --Where条件 

@GroupBy varchar(500) = NULL,      --分组 

@OrderBy varchar(500)= NULL,       --排序字段 

@PrimaryKeyField varchar(50),      --主键必需  

@PageIndex int = 1,            --开始页码 

@PageSize int = 10,             --页大小 

@IsCount bit = 0          --返回记录总数 

------------------------------------------------------------------------------------------------ 

--当@IsCount为1时,将同时返回2张表，表0为记录总数，表1为查询结果

------------------------------------------------------------------------------------------------ 

) 

AS 

BEGIN 

------------------------------------------------------------------------------------------------ 

DECLARE @strWhere nvarchar(500)                     --Where 条件 

IF @Where IS NOT NULL AND @Where != ''              --Where 条件 

BEGIN 

SET @strWhere = ' WHERE ' + @Where + ' ' 

END 

ELSE 

BEGIN 

SET @strWhere = '' 

END 

---------------------------------------------------------------------------------------------------- 

DECLARE @strGroupBy nvarchar(500)                   --GroupBy 条件 

IF @GroupBy IS NOT NULL AND @GroupBy != ''          --GroupBy 条件 

BEGIN 

SET @strGroupBy = ' GROUP BY ' + @GroupBy + ' ' 

END 

ELSE 

BEGIN 

SET @strGroupBy = '' 

END 

        ---------------------------------------------------------------------------------------------------- 

DECLARE @strOrderBy nvarchar(500)                   --OrderBy 条件 

IF @OrderBy IS NULL OR @OrderBy = ''                --OrderBy 条件 

BEGIN 

SET @strOrderBy = ' ORDER BY ' + @PrimaryKeyField + ' DESC' 

END 

ELSE 

BEGIN 

SET @strOrderBy = ' ORDER BY ' + @OrderBy 

END 

---------------------------------------------------------------------------------------------------- 

DECLARE @strSql nvarchar(max)   --Sql 语句 

--计算总行数 

IF @IsCount = 1 

BEGIN 

SET @strSql= 'SELECT  Count (*) AS RecordCount FROM ' + @Table + @strWhere + @strGroupBy 

EXEC sp_executesql @strSql 

--RETURN  

END  
                

---------------------------------------------------------------------------------------------------- 

IF @PageIndex < 1                                  --第一页提高性能 

BEGIN    

SET @PageIndex = 1 

END 

IF @PageIndex = 1                           

BEGIN 

SET @strSql = 'SELECT TOP ' + str(@PageSize) +  ' ' + @Field + ' FROM ' + @Table +  
                               @strWhere + @strGroupBy + @strOrderBy 

EXEC sp_executesql @strSql 

RETURN 

END 

----------------------------------------------------------------------------------------------------   

DECLARE @STARTID nvarchar(50) 

DECLARE @ENDID nvarchar(50) 

SET @STARTID = convert(nvarchar(50),(@PageIndex - 1) * @PageSize + 1) 

SET @ENDID = convert(nvarchar(50),@PageIndex * @PageSize) 

SET @strSql = 'WITH MYTABLE AS (SELECT ROW_NUMBER() OVER (' + @strOrderBy + ') 

AS RowNumber,' + @Field + ' FROM '+ @Table +  @strWhere + @strGroupBy + ')  
                           SELECT * FROM MYTABLE  

WHERE RowNumber BETWEEN ' + @STARTID + ' AND ' + @ENDID 

EXEC sp_executesql @strSql 

-------------------------------------------------------------------------------------------------- 

END