Create Procedure Common_Page

(

 @strTable varchar(500),

 @strSlt varchar(500)=null,

 @strKey varchar(500)='ID',

 @strWhere varchar(500) =null,

 @strOrder varchar(500)='ID desc',

 @PageSize int=20,

 @Page int=1,

 @RecordCount int output

)

as

declare @SQL nvarchar(4000)

if(@Page<1)

set @Page=1

declare @startID int

set @SQL=N'select @RC=count(*) from '+@strTable+' '+@strWhere

exec sp_executesql @SQL,N'@RC int output',@RecordCount output

--select @startID=id from adminlog order by id desc

set @startID=@PageSize*(@Page-1)+1

if(@startID>@RecordCount)

begin

        --返回空记录

    set @SQL=N'select '+@strSlt+' from '+@strTable+'where 1=0'

end

else

begin

    set @SQL='set RowCount '+ Convert(varchar(30),@startID) +'    '

    set @SQL=@SQL+'select @S='+@strKey+' from '+@strTable+' '+@strWhere+' order by '+@strOrder

    exec sp_executesql @SQL,N'@S int output',@startID output

    if(Len(Ltrim(@strWhere))>6)

        set @strWhere=@strWhere+ ' and '+@strKey+'<='+Convert(varchar(30),@startID)

    set @SQL='set RowCount '+ Convert(varchar(30),@PageSize) +'    '

    set @SQL=@SQL+'select '+@strSlt+' from '+@strTable+' '+@strWhere+' order by '+@strOrder

end

execute(@SQL)

 测试

DECLARE @RC int

DECLARE @strTable varchar(500)

DECLARE @strSlt varchar(500)

DECLARE @strKey varchar(500)

DECLARE @strWhere varchar(500)

DECLARE @strOrder varchar(500)

DECLARE @PageSize int

DECLARE @Page int

DECLARE @RecordCount int

SELECT @strTable = 'AdminLog l left join adminMenu m on L.Handle=m.id'

SELECT @strSlt = 'L.*,M.Name'

SELECT @strKey = 'l.id '

SELECT @strWhere = 'where l.areaid=0 '

SELECT @strOrder = 'l.id desc'

SELECT @PageSize = 50

SELECT @Page = 2

SELECT @RecordCount = NULL

EXEC @RC = [MainDB].[dbo].[Common_Page] @strTable, @strSlt, @strKey, @strWhere, @strOrder, @PageSize, @Page, @RecordCount OUTPUT

DECLARE @PrnLine nvarchar(4000)

PRINT '存储过程: MainDB.dbo.Common_Page'

SELECT @PrnLine = '    返回代码 = ' + CONVERT(nvarchar, @RC)

PRINT @PrnLine

PRINT '    输出参数: '

SELECT @PrnLine = '        @RecordCount = ' + isnull( CONVERT(nvarchar, @RecordCount), '<NULL>' )

PRINT @PrnLine