分页程序的总结

基于存储过程的分页程序的总结：

1.      基于单一主键和具有能够唯一确定一行数据的属性。

2.      判断传入的参数是否合法。例如：主键或主属性、每页显示的记录数、要显示的页数等。

3.      取得总的记录数目。

4.      判断传入页数，每页记录数的合法性并修改为相对应的合法值。

5.      构建SQL语句。

 

参考如下列

CREATE PROC P_viewPage

/*

适用于单一主键 或存在唯一值的表或视 ?

*/

@TableName VARCHAR(200), --表名

@FieldList VARCHAR(2000), --显示列名

@PrimaryKey VARCHAR(100), --单一主键 ˆ½唯一值 ?

@Where VARCHAR(1000), --查询条件不含'where'字符

@Order VARCHAR(1000), --排序不含'order by'字符，如id asc,userid desc，当@SortType=3时生效

@SortType INT, --排序规 ?1:正序asc 2:倒序desc 3:多列排序

@RecorderCount INT, --记录总数0:会返回总 ?录

@PageSize INT, --每 ?输出的记 ?数

@PageIndex INT, --当前页 ”

@TotalCount INTEGER OUTPUT, --返回记录总 ”

@TotalPageCount INTEGER OUTPUT --返回总 ?数

 

AS

SET NOCOUNT ON

IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = '' 

OR ISNULL(@PrimaryKey,'') = ''

OR @SortType < 1 OR @SortType >3

OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0

BEGIN 

    RETURN

END

 

DECLARE @new_where1 VARCHAR(1000)

DECLARE @new_where2 VARCHAR(1000)

DECLARE @new_order VARCHAR(1000) 

DECLARE @Sql VARCHAR(8000)

DECLARE @SqlCount NVARCHAR(4000)

 

IF ISNULL(@where,'') = ''

    BEGIN

         SET @new_where1 = ' '

         SET @new_where2 = ' WHERE '

    END

ELSE

    BEGIN

         SET @new_where1 = ' WHERE ' + @where 

         SET @new_where2 = ' WHERE ' + @where + ' AND '

    END

--**************************************************************************************

IF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2 

    BEGIN

              IF @SortType = 1 SET @new_order = ' ORDER BY ' + @PrimaryKey + ' ASC'

              IF @SortType = 2 SET @new_order = ' ORDER BY ' + @PrimaryKey + ' DESC'

    END

ELSE

    BEGIN

              SET @new_order = ' ORDER BY ' + @Order

    END

--**************************************************************************************

SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'

+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1

--**************************************************************************************

IF @RecorderCount = 0

    BEGIN

              EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',

              @TotalCount OUTPUT,@TotalPageCount OUTPUT

    END

ELSE

    BEGIN

              SELECT @TotalCount = @RecorderCount 

    END

--**************************************************************************************

IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)

    BEGIN

         SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)

         --这里加一行测 ?

         --print '当前页为+str(@PageIndex)

    END

--**************************************************************************************

 

IF @PageIndex = 0 or @PageIndex = 1 

--IF @PageIndex = 1    --原始行

    BEGIN

         SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 

         + @TableName + @new_where1 + @new_order

    END

ELSE

    BEGIN

         IF @SortType = 1

                BEGIN

                     SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 

                     + @TableName + @new_where2 + @PrimaryKey + ' > '

                     + '(SELECT max(' + @PrimaryKey + ') FROM (SELECT TOP '

                     + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey 

                     + ' FROM ' + @TableName

                     + @new_where1 + @new_order +' ) AS TMP) '+ @new_order

                END

         IF @SortType = 2

                BEGIN

                     SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 

                     + @TableName + @new_where2 + @PrimaryKey + ' < '

                     + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '

                     + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey 

                     +' FROM '+ @TableName

                     + @new_where1 + @new_order + ') AS TMP) '+ @new_order 

                END 

         IF @SortType = 3

                BEGIN

                     IF CHARINDEX(',',@Order) = 0

                            BEGIN

                                   RETURN

                            END

                     SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '

                     + @TableName + @new_where2 + @PrimaryKey + ' NOT IN (SELECT TOP '

                     + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey

                     + ' FROM ' + @TableName + @new_where1 + @new_order + ')'

                     + @new_order

                END

    END