SQL Server分页存储过程
create procedre up_GetDataByPage(
@pageSize int --每页显示记录数
@curPage int --当前页
@condition varchar(max) --筛选条件
@count int output --输出参数，总记录数
)
--获取总记录数
declare @temp_sql varchar(max)
set @temp_sql='select @temp=count(*) from dt_name where '+@condition
exec sp_executesql @temp_sql,N'@temp int output',@count output
declare @begin int,@end int
set @begin=(@curPage-1)*@pageSize+1
set @end=@curPage*@pageSize
declare @sql varchar(max)
set @sql='select * from (select *,ROW_NUMBER() OVER(order by id desc) as num from dt_name where '+@condition+' ) a where a.num between '+CONVERT(varchar(10),@begin)+' and '+CONVERT(varchar(10),@end)
exec(@sql)
注意：字符串拼接时，必须将数值类型转换成字符串类型
页:
[1]