高效通用分页存储过程

 

1.大量数据的

/****** Object:  StoredProcedure [dbo].[DataPaging]    Script Date: 11/30/2012 16:27:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[DataPaging]
(
    @Table VARCHAR(450),             --表名，可以是多个表，但不能用别名
    @PrimaryKey VARCHAR(50),         --主键，可以为空，但@Order为空时该值不能为空
    @Fields    VARCHAR(800),         --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
    @Pagesize INT,
    @Pageindex INT,
    @Where VARCHAR(800) = '',        --条件，可以为空，不用填 where
    @Order VARCHAR(200) = '',        --排序，可以为空，为空默认按主键升序排列，不用填 order by
    @RecordCount  int
)
as
set nocount on
DECLARE @ListCount INT
DECLARE @pos INT
IF @Fields = ''
    SET @Fields = '*'
IF @Where <> ''
    SET @Where = 'WHERE ' +  @Where
IF @Order = '' OR @Order = null
    SET @Order = @PrimaryKey + ' DESC'

SET @Order = REPLACE(REPLACE(@Order, 'asc', 'ASC'), 'desc', 'DESC')   
/*第一页*/
if(@Pageindex=1)
    exec('select  top '+@Pagesize+@Fields +' from ' + @Table+' '+@Where+' order by ' + @Order)
else
begin
    declare @PageUpperBound int
    declare @endrecords int
    DECLARE @TempOrderA VARCHAR(200), @TempOrderB VARCHAR(200)
    DECLARE @SortName VARCHAR(100),@SortTable VARCHAR(100),@SortNameTmp VARCHAR(100)
    DECLARE @TableNameA  VARCHAR(100),@TableNameB  VARCHAR(100)
    
    set @PageUpperBound=@Pageindex*@Pagesize
    /*转换排序开始*/
        IF CHARINDEX(',', @Order) > 0
            begin
                set @pos = 0
                SET @TempOrderA = @Order
                WHILE (CHARINDEX(',', @Order,@pos)>0)
               BEGIN
                    SET @SortName = SUBSTRING(@Order, @pos, (CHARINDEX(',', @Order,@pos)-@pos))
                    SET @SortTable = SUBSTRING(@SortName, 0, CHARINDEX('.', @SortName))
                    SET @SortNameTmp = REPLACE(@SortName,  @SortTable, 'A')

                    IF CHARINDEX('DESC',@SortNameTmp) > 0
                        BEGIN
                            SET @SortNameTmp = REPLACE(@SortNameTmp, 'DESC','ASC')
                        END
                    ELSE IF CHARINDEX('ASC',@SortNameTmp) > 0
                        BEGIN
                            SET @SortNameTmp = REPLACE(@SortNameTmp, 'ASC','DESC')
                        END
                    ELSE
                        BEGIN
                            SET @SortNameTmp = @SortNameTmp + ' DESC'
                        END

                    SET @TempOrderA = REPLACE(@TempOrderA,  @SortName, @SortNameTmp)
                    SET @TempOrderB = REPLACE(@Order,  @SortTable, 'B')
                    SET @pos = CHARINDEX(',', @Order,@pos) + 1
                END
                
                SET @SortName = SUBSTRING(@Order, @pos, (LEN(@Order)-@pos+1))
                SET @SortTable = SUBSTRING(@SortName, 0, CHARINDEX('.', @SortName))
                SET @SortNameTmp = REPLACE(@SortName,  @SortTable, 'A')
                IF CHARINDEX('DESC',@SortNameTmp) > 0
                    BEGIN
                        SET @SortNameTmp = REPLACE(@SortNameTmp, 'DESC','ASC')
                    END
                ELSE IF CHARINDEX('ASC',@SortName) > 0
                    BEGIN
                        SET @SortNameTmp = REPLACE(@SortNameTmp, 'ASC','DESC')
                    END
                ELSE
                    BEGIN
                        SET @SortNameTmp = @SortNameTmp + ' DESC'
                    END
                SET @TempOrderA = REPLACE(@TempOrderA,  @SortName, @SortNameTmp)
                SET @TempOrderB = REPLACE(@TempOrderB,  @SortTable, 'B')
            end
      else
            begin
            SET @SortTable = SUBSTRING(@Order, 0, CHARINDEX('.', @Order))
            SET @TempOrderA = REPLACE(@Order,  @SortTable, 'A')
            SET @TempOrderB = REPLACE(@Order,  @SortTable, 'B')
            IF CHARINDEX('DESC',@TempOrderA) > 0
            BEGIN
                SET @TempOrderA = REPLACE(@TempOrderA, 'DESC','ASC')
            END
            ELSE IF CHARINDEX('ASC',@TempOrderA) > 0
            BEGIN
                SET @TempOrderA = REPLACE(@TempOrderA, 'ASC','DESC')
            END
            ELSE
            BEGIN
                SET @TempOrderA = @TempOrderA + ' DESC'
            END
        end
    /*转换排序结束*/

    if(@RecordCount-(@PageUpperBound-@Pagesize)<=@Pagesize)
        begin
            set @endrecords=@RecordCount-(@PageUpperBound-@Pagesize)
            IF CHARINDEX(',', @Order) > 0
                BEGIN
                    DECLARE @TMP1 VARCHAR(50),@TMP2 VARCHAR(50)
                    set @pos = 0
                    WHILE (CHARINDEX(',', @Order,@pos)>0)      --CHARINDEX(',', @Order,@pos)>0
                    BEGIN
                        SET @TMP1 = SUBSTRING(@Order, @pos, CHARINDEX(',', @Order,@pos))
                        if  CHARINDEX('ASC', @TMP1) > 0
                        begin
                            SET @TMP2 = REPLACE(@TMP1, 'ASC','DESC')
                        end
                        else if CHARINDEX('DESC', @TMP1) > 0
                        begin
                            SET @TMP2 = REPLACE(@TMP1, 'DESC','ASC')
                        end
                        else
                        begin
                            SET @TMP2 = @TMP1 + ' DESC'
                        end
                        SET @Order =  REPLACE(@Order,  @TMP1, @TMP2)
                        SET @pos = CHARINDEX(',', @Order,@pos) + 1
                    END

                    SET @TMP1 = SUBSTRING(@Order, @pos, (LEN(@Order)-@pos+1))
                    if  CHARINDEX('ASC', @TMP1) > 0
                        begin
                            SET @TMP2 = REPLACE(@TMP1, 'ASC','DESC')
                        end
                    else if CHARINDEX('DESC', @TMP1) > 0
                        begin
                            SET @TMP2 = REPLACE(@TMP1, 'DESC','ASC')
                        end
                    else
                        begin
                            SET @TMP2 = @TMP1 + ' DESC'
                        end
                    SET @Order = REPLACE(@Order,  @TMP1, @TMP2)
                END
            ELSE
                BEGIN
                    if  CHARINDEX('ASC', @Order) > 0
                        begin
                            SET @Order = REPLACE(@Order, 'ASC','DESC')
                        end
                    else if CHARINDEX('DESC', @Order) > 0
                        begin
                            SET @Order = REPLACE(@Order, 'DESC','ASC')
                        end
                    else
                        begin
                            SET @Order = @Order + ' DESC'
                        end
                END
            exec('select * from ( select  top '+@endrecords+@Fields+'  from ' + @Table+'  '+@Where+' order by ' + @Order + ')B order by ' + @TempOrderB)
        end
    else
        Begin
            exec('select * from (select top '+@Pagesize+' * from (select  top '+@PageUpperBound
            +@Fields+'  from ' + @Table
            +' '+@Where+' order by '+@Order+')A order by '+@TempOrderA+')B order by '+ @TempOrderB )
        End
end
set nocount off

 

2.少量数据的

/****** Object:  StoredProcedure [dbo].[DoSplitPage]    Script Date: 11/30/2012 16:44:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[DoSplitPage]
@Sql nvarchar(4000), --要执行的sql语句
@PageNumber int=1,   --起始页码
@PageSize int,  --每页记录数
@RecordCount int output --总记录数
as
/*
declare @i int
exec DoSplitPage 'select * from Map_ElementHot',10,20,@i output
print @i
*/
set nocount on
declare @p1 int  --游标的id
declare @count int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@count output
set @RecordCount = @count
set @PageNumber=(@PageNumber-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@PageNumber,@PageSize 
exec sp_cursorclose @p1
set nocount off