Sql Server存储过程分页

--分页存储过程
if OBJECT_ID('pro_Show','P') is not null
drop proc pro_Show
go
create proc pro_Show
(
@_Fileds varchar(20), --表字段
@_Table varchar(20), --表名
@_Filter varchar(20), --查询条件
@_Oderby varchar(20), --排序字段
@_PageIndex int, --当前页数
@_PageSize int, --页面记录数
@_Total int output --总记录数
)
as
declare @startrow int , @endrow int --定义开始行 结束行
set @startrow = (@_PageIndex-1)*@_PageSize +1 --开始行赋值
set @endrow = @_PageIndex*@_PageSize --结束行赋值
declare @sql1 nvarchar(3000),@sql2 nvarchar(3000)--定义分页SQL字符串

--select * from (select ROW_NUMBER() over(order by Id) as rownum ,* from MyService where Name = 'a' ) p
--where rownum between @startrow and @endrow select @_Total = count(*) from MyService where Name = 'a'

set @sql1 = 'select * from (select ROW_NUMBER() over(order by '+@_Oderby+') as rownum ,'+@_Fileds+' from '+@_Table+' where '+@_Filter+' ) p
where p.rownum between '+convert(varchar,@startrow)+' and '+convert(varchar,@endrow)+'
select @_Total = count(*) from '+@_Table+' where '+@_Filter+'' --拼写分页SQL

set @sql2 = '@_Total int output' --总记录数定义字符串

exec SP_ExecuteSQL @sql1,@sql2,@_Total output --执行SQL
print @sql1 --打印SQL1
print @sql2 --打印SQL2
--调用存储过程
declare @Count int
exec pro_Show '*','MyService','1=1','Id',1,2,@Count output
select @Count