存储过程分页
alter procedure Page
@TableName varchar(255),--表名
@ID varchar(50), --主表的主键
@StrGetFields varchar(1000) = '*', --需要返回的列
@PageSize varchar(10),--页尺寸
@PageIndex varchar(10),--页码
@StrWhere varchar(1500), --查询条件
@PxName varchar(255), --排序的字段
@OrderType varchar(1)='a', --设置排序类型,0:asc,1:desc
@Count varchar(50) --返回记录总数
as
set nocount on
declare @strSQL varchar(5000) --主语句
declare @strTmp varchar(110) --临时变量
declare @strOrderBy varchar(1000) --排序字符串
set @Count=''
set @Count = 'select count(*) from '+ @TableName
if @PageSize = ''
set @PageSize = '10'
if @PageIndex = ''
set @PageIndex = '1'
if @PxName != '' begin
if @OrderType = 'a'
set @strOrderBy = ' order by '+@PxName+' asc '
else
set @strOrderBy = ' order by '+@PxName+' desc '
end
else
set @strOrderBy = ''
if @StrWhere != ''
set @StrWhere = ' 1=1 and ' + @strWhere
else
set @StrWhere = ' 1=1 '
set @strSQL= 'select top '+@PageSize+' '+@StrGetFields+' from '+@TableName+' where '
+ ''+@ID+' not in (select top (('+@PageIndex+'-1)*'+@PageSize+') '+@ID+' from '+@TableName+' where '+@StrWhere+' '+@strOrderBy+') '
+ ' and ' + @StrWhere + ' '+@strOrderBy+' '
exec (@strSQL)
go
exec Page 'zu_corporation','corID','corID,corName,corEcotype','10','4','','','',''
-- corName like ''%广东%''
--select * from zu_corporation order by corID desc
--select top 10 corID,corName,corEcotype from zu_corporation
--where corID not in (select top ((1-1)*10) corID from zu_corporation where corName like '%广东%') and corName like '%广东%'