Sql2005过程分页

转载

mb5fe55a1c73221 2021-09-16 22:29:00

文章标签 sql asp.net 跨平台微信公众号 c# 文章分类 数据库

ALTER PROCEDURE [dbo].[procPageChangeForSql2005]
(
 @fromSql varchar(4000),
 @querySql varchar(4000),
 @whereSql varchar(max),
 @orderBySql varchar(200),
 @keyField varchar(100),
 @PageSize int,                     --每页多少条记录
 @PageIndex int,               --指定当前为第几页
 @TotalRecord int output            --返回总记录数
)
AS
BEGIN
    declare @sql nvarchar(max)
    declare @totalCountSql nvarchar(MAX)
    declare @StartRecord int
    declare @EndRecord int declare @nPageCount int --处理开始点和结束点
    IF (@whereSql! = '' AND @whereSql IS NOT NULL)
        set @whereSql=' where '+ @whereSql
    else set @whereSql=''
    --计算总条数
    set @totalCountSql= N'select @TotalRecord = count(*) from ' + @fromSql+@whereSql --总记录数语句
    exec sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数
    if(@PageSize>-1)
    begin     set @nPageCount =CONVERT(int, ceiling(@TotalRecord*1.0/@PageSize))
        if(@nPageCount=0 and @PageIndex != 0)
        set @PageIndex = 0
        else if (@PageIndex > (@nPageCount - 1))
        set @PageIndex = 0
        set @StartRecord = (@PageIndex)*@PageSize + 1
        set @EndRecord = @StartRecord + @PageSize - 1  --组织Sql语句 set @sql ='(select row_number() over (order by '+ @orderBySql +') as rowId,'+@querySql+' from '+ @fromSql+@whereSql;--查询语句 set @sql ='select * from ' + @sql + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' +  ltrim(str(@EndRecord));
    end
    else
    begin IF (@orderBySql! = '' AND @orderBySql IS NOT NULL) set @orderBySql=' order by '+ @orderBySql else set @orderBySql='' set @sql='select '+@querySql+' from '+ @fromSql+@whereSql+@orderBySql
    end
    Exec(@sql)
END