存储过程02-简单通用分页

一时失言乱红尘 2022-04-12 06:12 263阅读 0赞

create PROCEDURE [dbo].[GetPageDataOutRowNumber]
    (
    @tn nvarchar(30),--表名称
    @idn nvarchar(20),--表主键名称
    @pi int = 1,--当前页数
    @ps  int = 7,--每页大小
    @wh nvarchar(255) = '',--wehre查询条件
    @oby nvarchar(255) = '',--orderby 排序
    @rc int output,--总行数(传出参数)
    @pc int output--总页数(传出参数)
    )
    AS
    DECLARE @sql NVARCHAR(225)='',@sqlCount NVARCHAR(225)=''
    --1.计算总行数和总页数
    SET @sqlCount = 'SELECT @rc=COUNT(['+@idn+']),@pc=CEILING((COUNT('+@idn+')+0.0)/'+ CAST(@ps AS VARCHAR)+') FROM ' + @tn
    IF LEN(@wh)>1
        set @sqlCount=@sqlCount+' WHERE '+@wh
    print @sqlCount
    EXEC SP_EXECUTESQL @sqlCount,N'@rc INT OUTPUT,@pc INT OUTPUT',@rc OUTPUT,@pc OUTPUT
    --2.分页
    --2.1如果是第一页，则直接查询
    IF @pi = 1
    BEGIN
        SET @sql='SELECT TOP '+str(@ps) +' * FROM '+@tn
        IF LEN(@wh)>1
            set @sql=@sql+' WHERE '+@wh
        IF LEN(@oby)>1
                SET @sql=@sql+' order by ' +@oby
    EXEC(@sql)
    END
    ELSE--2.2如果不是第一页，则拼接查询语句
    BEGIN
        SET NOCOUNT ON
        SET @sql='SELECT * FROM (select row_number() over(order by '
        IF LEN(@oby)>1
            set @sql=@sql + @oby+') as rowNum,* from '+@tn
        else
            set @sql=@sql + @idn+') as rowNum,* from '+@tn
        IF LEN(@wh)>1
            set @sql=@sql+' where '+@wh
        set @sql=@sql+')as temp where rowNum>'+str(@ps * (@pi-1))+' and rowNum<='+str(@ps*@pi)
        print @sql
        EXEC(@sql)
        SET NOCOUNT OFF
    END
    
    --测试语句
    declare @rc int,@pc int
    exec [GetPageDataOutRowNumber] 'Ams_Area','ar_id',2,5,'',' ar_id desc',@rc output,@pc output
    select @rc,@pc