–用于通用表格分页(超强版无主键分页!主键可重复)
本存储过程只能在大于sql2005以上版本使用
虽然短短几句话却浓缩我很多精妙之处**
Create proc [dbo].[PagingSql] (@start varchar(10),@limit varchar(10),@id varchar(10),@Sql varchar(6000)) --起始位置,--显示数量,--表id--sql语句
as
set @Sql='select * into #tempSql from ('+ @Sql +') as temp
;with cte as(
select *,row_number() over(order by ' + @id + ') as rn from #tempSql
)select * from cte where rn between ' + @start + ' and ' + @limit + ''
exec (@sql)
——调用方法
exec PagingSql '0','25','id','select * from books'