通用存储过程

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