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
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