alter proc divide_page
@pagesize int=10,--页大小
@pagenum int=0, --页号
@rowcount int=0 output,--总行数
@tb_name nvarchar(50)=''--表名
as
begin
declare @SQL nvarchar(255),@SQLcount nvarchar(255),@first nvarchar(50),@last nvarchar(50)
set @first=cast(((@pagenum-1)*@pagesize) as nvarchar(50))
set @last=cast((@pagenum*@pagesize-1) as nvarchar(50))
set @SQLcount='select @rowcount=count(Id) from '+@tb_name
set @SQL='select * from ( select ROW_NUMBER() over (order by Id) as rownum,* from ' + @tb_name + ') as t where t.rownum between ' + @first+ ' and '+@last
exec sp_executesql @SQLcount,N'@rowcount int output',@rowcount output
exec sp_executesql @SQL
end
下面是调用该存储的一个方法,最主要的就是参数写对就可以了,在这里就不多说了。
declare @rowco int
exec divide_page 10,2,@rowco,'BOOK'
print @rowco