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) --默认的IDasdeclare @sql nvarchar(1800)declare @startid intdeclare @endid intdeclare @pagecount int
if @condition is null or @condition = '' --判断条件是否为空来拼接查询语句 set @sql = N'select @returnValue=count(*) from ' + @tabNameelse 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 / @pagesizeif @returnValue % @pagesize <> 0 set @pagecount = @pagecount + 1if @currentpage > @pagecount set @currentpage = @pagecountset @startid = (@currentpage - 1) * @pagesize + 1set @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 ' + @tabNameelse 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 @sqlreturn @returnValuego
