分页存储过程-CSDN博客

本文链接：https://blog.csdn.net/yale28/article/details/1834412

据说分页存储过程效率很高，很好用，以前没有用过今天刚接触

在这里整理了几个可以用的分页存储过程，以备后用。

一：

CREATE PROCEDURE usp_PagingLarge
@TableNames VARCHAR(300),    --表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR(100),    --主键，可以为空，但@Order为空时该值不能为空
@Fields    VARCHAR(350),        --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize INT,            --每页记录数
@CurrentPage INT,        --当前页，0表示第1页
@Filter VARCHAR(200) = '',    --条件，可以为空，不用填 where
@Group VARCHAR(200) = '',    --分组依据，可以为空，不用填 group by
@Order VARCHAR(200) = '',   --排序，可以为空，为空默认按主键升序排列，不用填 order by
@RecordCount int = 0 output
AS
BEGIN
    DECLARE @SortColumn VARCHAR(200)
    DECLARE @Operator CHAR(2)
    DECLARE @SortTable VARCHAR(200)
    DECLARE @SortName VARCHAR(200)
    DECLARE @TmpSelect NVarchar(200)
    IF @Fields = ''
        SET @Fields = '*'
    IF @Filter = ''
        SET @Filter = 'WHERE 1=1'
    ELSE
        SET @Filter = 'WHERE ' + @Filter
    IF @Group <>''
        SET @Group = 'GROUP BY ' + @Group

    IF @Order <> ''
    BEGIN
        DECLARE @pos1 INT, @pos2 INT
        SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
        IF CHARINDEX(' DESC', @Order) > 0
            IF CHARINDEX(' ASC', @Order) > 0
            BEGIN
                IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
                    SET @Operator = '<='
                ELSE
                    SET @Operator = '>='
            END
            ELSE
                SET @Operator = '<='
        ELSE
            SET @Operator = '>='
        SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
        SET @pos1 = CHARINDEX(',', @SortColumn)
        IF @pos1 > 0
            SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
        SET @pos2 = CHARINDEX('.', @SortColumn)
        IF @pos2 > 0
        BEGIN
            SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
            IF @pos1 > 0
                SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
            ELSE
                SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
        END
        ELSE
        BEGIN
            SET @SortTable = @TableNames
            SET @SortName = @SortColumn
        END
    END
    ELSE
    BEGIN
        SET @SortColumn = @PrimaryKey
        SET @SortTable = @TableNames
        SET @SortName = @SortColumn
        SET @Order = @SortColumn
        SET @Operator = '>='
    END

    DECLARE @type varchar(50)
    DECLARE @prec int
    SELECT @type=t.name, @prec=c.prec
    FROM sysobjects o
    JOIN syscolumns c on o.id=c.id
    JOIN systypes t on c.xusertype=t.xusertype
    WHERE o.name = @SortTable AND c.name = @SortName
    IF CHARINDEX('char', @type) > 0
    SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

    DECLARE @TopRows INT
    SET @TopRows = @PageSize * @CurrentPage + 1
    print @TopRows
    print @Operator
    EXEC('
        DECLARE @SortColumnBegin ' + @type + '
        SET ROWCOUNT ' + @TopRows + '
        SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
        SET ROWCOUNT ' + @PageSize + '
        SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '
    ')
    DECLARE @str_Count_SQL nvarchar(500)
    SET @str_Count_SQL= 'SELECT @TotalCount=count('+@PrimaryKey+') FROM ' + @TableNames + ' ' + @Filter
    EXEC sp_executesql @str_Count_SQL,N'@TotalCount int=0 output',@RecordCount output
End
GO

===========================================================================

二：

ALTER procedure Consignment
@tablename varchar(80) ,
@strOrder varchar(50) ,
@PageIndex int = 1,
@PageSize int = 15,
@strGetFields varchar(200) = '*',
@OutPut int output
as
Begin
Declare @strSql varchar(500)

DECLARE   @SQL   NVARCHAR(1000)
DECLARE   @R BIGINT
SET   @SQL= N'select @R=count(*) from '+@TableName
EXEC SP_EXECUTESQL   @SQL, N' @R BIGINT OUTPUT', @R OUTPUT
SET   @OutPut= @R

if(@PageIndex =1)
Begin
   set @strSql='select top '+str(@PageSize)+' '+@strGetFields+' from '+@tablename+' order by '+@strOrder
End
Else
   set @strSql='select top '+str(@PageSize)+' '+@strGetFields+' from '+@tablename+' where ('+@strOrder
             +' >= ( select Max('+@strOrder+') from ( select top '+str(@PageSize*@PageIndex)+' * from '+@tablename+' order by '
             +@strOrder+' ) as tempTable)) order by '+ @strOrder
   select @strSql
exec(@strSql)

End

三：

alter procedure AllProce
@tablename varchar(200) ,   --表名
@strGetFields varchar(200) = '*',  --查询列名
@PageIndex int = 1 ,         --页码
@pageSize int = 15,         --页面大小
@strWhere  varchar(100) = '',     --查询条件
@strOrder varchar(100) = '', --排序列名
@intOrder bit = 0,        --排序类型  1为升序
@CountAll bigint output              --返回纪录总数用于计算页面数
as
begin
declare @strSql varchar(500)  --主语句
declare @strTemp varchar(100) --临时变量
declare @strOrders varchar(50) --排序语句
declare @table varchar(70)

declare   @SQL   nvarchar(1000)
declare   @R bigint
set   @SQL=  N'select @R=count(*) from  '+convert(nvarchar(200),@TableName)
exec  SP_EXECUTESQL   @SQL,  N' @R BIGINT OUTPUT',  @R OUTPUT
set   @CountAll=  @R
if @intOrder = 0
begin
    --为0是升序
    set @strTemp = '>(select max'
    set @strOrders =  ' order by  '+@strOrder+' asc '
end
else
begin
    --否则为降序
    set @strTemp = '<(select min'
    set @strOrders = ' order by  '+@strOrder+' desc '
end
if @PageIndex =1        --第一页直接读出纪录
begin
    if @strWhere = ''
    begin
         set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' '+@strOrders
     end
    else
    begin
         set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+ ' from '+@tablename+' where '+@strWhere+' '+@strOrders

    end
end
else
begin
    set @strSql = 'select top'+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strOrder+' '+@strTemp+' ('+@strOrder+')'
                  +' from (select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+ ' '+@strOrders+ ') as tempTable ) '+@strOrders

    if @strWhere != ' '
    begin
       set @strSql = 'select top '+str(@pageSize)+ ' '+@strGetFields+' from '+@tablename+ ' where '+@strOrder+ ' '+@strTemp+' ('+@strOrder+') '
                   +' from(select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strWhere+' ' +@strOrders+') as tempTable) where '+@strWhere+' '+@strOrders

    end
end
exec(@strSql)
end
GO