CREATE procedure [dbo].[proc_GetPageOfRecords]
@tabName varchar(200), --表名如:stuInfo a,stuInfo b
@columns varchar(1000) = '*', --查询的列名如:a.id,b.stuName
@condition varchar(1000) = '', --查询条件如:不需要加where
@ascColumn varchar(100) = '', --排序的字段名(id desc等等),如果为空则根据主键ID倒序来排列
@returnValue int = -1 output, --返回值
@pageSize int, --每页显示大小
@currentPage int, --当前页
@primaryId varchar(20) --默认的ID
as
declare @sql nvarchar(1800)
declare @startid int
declare @endid int
declare @pagecount int
if @condition is null or @condition = '' --判断条件是否为空来拼接查询语句
set @sql = N'select @returnValue=count(*) from ' + @tabName
else
set @sql = N'select @returnValue=count(*) from ' + @tabName + ' where ' + @condition
exec sp_executesql @sql,N'@returnValue int output',@returnValue output
if @returnValue is null
set @returnValue=0
if @ascColumn = '' --如果为空,根据主键ID倒序排列
set @ascColumn = @primaryId + ' desc '
set @pagecount = @returnValue / @pagesize
if @returnValue % @pagesize <> 0
set @pagecount = @pagecount + 1
if @currentpage > @pagecount
set @currentpage = @pagecount
set @startid = (@currentpage - 1) * @pagesize + 1
set @endid = @startid + @pagesize - 1
if @condition is null or @condition = ''
set @sql = N'select * from(select '+@columns+ ',ROW_NUMBER() OVER (order by '+ @ascColumn +') as pos from ' + @tabName
else
set @sql = N'select * from(select '+@columns+ ',ROW_NUMBER() OVER (order by '+ @ascColumn +') as pos from ' + @tabName + ' where ' + @condition
set @sql = @sql + ') as t where t.pos between ' + convert(varchar(10),@startid) + ' and ' + convert(varchar(10),@endid)
exec sp_executesql @sql
return @returnValue
go