if object_id('proc_selectStuAndPagging') is not null
drop proc proc_selectStuAndPagging
go
create proc proc_selectStuAndPagging
@tbl nvarchar(10),--表名
@orderWord nvarchar(20),--排序
@fields nvarchar(100),--字段
@whereCondition nvarchar(200),--查询条件
@pageSize int,--每页记录条数
@pageNum int,--当前页码
@totalCount nvarchar(20) output--满足查询条件的总记录条数
as
declare @sqlResult nvarchar(700),@sqlCount nvarchar(700),@sql nvarchar(350)
--综合拼接查询字符串
set @sqlCount='select @rowsCount=count(*) '
set @sqlResult='select * '
set @sql=' from(select row_number() over(order by '+ @orderWord +') as rowNum,'+ @fields +' from '+ @tbl +' where 1=1'
if @whereCondition<>''
set @sql=@sql+@whereCondition
set @sql=@sql+')A'
--查询记录数的SQL语句
set @sqlCount= @sqlCount+@sql
set @sql=@sql+' where rowNum between '+str((@pageNum-1)*@pageSize+1)+' and '+str(@pageSize*@pageNum)
--查询满足条件的当前页数据
set @sqlResult=@sqlResult+@sql
declare @rowsCount int,@finalSql nvarchar(max)
declare @paras nvarchar(100)
set @paras ='@rowsCount as int output'
set @finalSql=@sqlResult+@sqlCount
exec sp_executesql @finalSql,@paras,@rowsCount=@totalCount output
go
----测试语句
declare @count int
exec proc_pager 'admin','adminId asc','*','',1,5,@count output
print '总条数:'+str(@count)
go