第一:
ALTER procedure Consignment
@tablename varchar(80) ,
@strOrder varchar(50) ,
@PageIndex int = 1,
@PageSize int = 15,
@strGetFields varchar(200) = '*',
@OutPut int output
as
Begin
Declare @strSql varchar(500)
DECLARE @SQL NVARCHAR(1000)
DECLARE @R BIGINT
SET @SQL= N'select @R=count(*) from '+@TableName
EXEC SP_EXECUTESQL @SQL, N' @R BIGINT OUTPUT', @R OUTPUT
SET @OutPut= @R
if(@PageIndex =1)
Begin
set @strSql='select top '+str(@PageSize)+' '+@strGetFields+' from '+@tablename+' order by '+@strOrder
End
Else
set @strSql='select top '+str(@PageSize)+' '+@strGetFields+' from '+@tablename+' where ('+@strOrder
+' >= ( select Max('+@strOrder+') from ( select top '+str(@PageSize*@PageIndex)+' * from '+@tablename+' order by '
+@strOrder+' ) as tempTable)) order by '+ @strOrder
select @strSql
exec(@strSql)
End
第二:
alter procedure AllProce
@tablename varchar(200) , --表名
@strGetFields varchar(200) = '*', --查询列名
@PageIndex int = 1 , --页码
@pageSize int = 15, --页面大小
@strWhere varchar(100) = '', --查询条件
@strOrder varchar(100) = '', --排序列名
@intOrder bit = 0, --排序类型 1为升序
@CountAll bigint output --返回纪录总数用于计算页面数
as
begin
declare @strSql varchar(500) --主语句
declare @strTemp varchar(100) --临时变量
declare @strOrders varchar(50) --排序语句
declare @table varchar(70)
declare @SQL nvarchar(1000)
declare @R bigint
set @SQL= N'select @R=count(*) from '+convert(nvarchar(200),@TableName)
exec SP_EXECUTESQL @SQL, N' @R BIGINT OUTPUT', @R OUTPUT
set @CountAll= @R
if @intOrder = 0
begin
--为0是升序
set @strTemp = '>(select max'
set @strOrders = ' order by '+@strOrder+' asc '
end
else
begin
--否则为降序
set @strTemp = '<(select min'
set @strOrders = ' order by '+@strOrder+' desc '
end
if @PageIndex =1 --第一页直接读出纪录
begin
if @strWhere = ''
begin
set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' '+@strOrders
end
else
begin
set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+ ' from '+@tablename+' where '+@strWhere+' '+@strOrders
end
end
else
begin
set @strSql = 'select top'+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strOrder+' '+@strTemp+' ('+@strOrder+')'
+' from (select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+ ' '+@strOrders+ ') as tempTable ) '+@strOrders
if @strWhere != ' '
begin
set @strSql = 'select top '+str(@pageSize)+ ' '+@strGetFields+' from '+@tablename+ ' where '+@strOrder+ ' '+@strTemp+' ('+@strOrder+') '
+' from(select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strWhere+' ' +@strOrders+') as tempTable) where '+@strWhere+' '+@strOrders
end
end
exec(@strSql)
end
GO