sqlserver 分页存储过程 create proc [dbo].[proc_opinion_baseinfo] @tablename varchar(4000)， @pkfield varchar(100)， @pageindex int=1, @pagesize int=10, @sqlwhere nvarchar(4000)， @rowcount bigint output, @pagecount bigint output as if(@sql
sqlserver 分页存储过程
create proc [dbo].[proc_opinion_baseinfo]
@tablename varchar(4000)，
@pkfield varchar(100)，
@pageindex int=1,
@pagesize int=10,
@sqlwhere nvarchar(4000)，
@rowcount bigint output,
@pagecount bigint output
as
if(@sqlwhere='1')
set @sqlwhere = '1=1'
declare @sql nvarchar(4000)，，@start int,@end int
set @sql='select * from (select row_number() over(order by '+@pkfield+' desc) rowid,* from '+@tablename+' where '+@sqlwhere
set @start = (@pageindex-1)*@pagesize+1
set @end = @start+@pagesize-1
set @sql = @sql + ') t where rowid between '+cast(@start as varchar(20))+' and ' +cast(@end as varchar(20))
exec (@sql)
set @sql = 'select @rowcount=count(1) from '+@tablename+' where '+@sqlwhere
exec sp_executesql @sql,n'@rowcount bigint output',@rowcount output
if(@rowcount%@pagesize=0)
begin
set @pagecount = @rowcount / @pagesize
end
else
begin
set @pagecount = @rowcount / @pagesize +1
end