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)

注意：字符串拼接时，必须将数值类型转换成字符串类型