分页 存储过程
CREATE proc newgetpage
(--默认分页主键为id
@tblName nvarchar(128), --表名称列表
@PageSize int=10, --页尺寸
@PageIndex int=1, --当前页
@fields nvarchar(4000)='*', --查询字段列表
@fldname nvarchar(50), --主键字段名
@fldorder bit=1, --主键排序方式，0asc，非零desc
@FirstfldName nvarchar(50)='', --主排序字段名，非主键，有重复值
@FirstfldOrder bit=1, --主字段排序方式
@SecondfldName nvarchar(50)='', --副排序字段名，非主键，有重复值
@SecondfldOrder bit=1, --副字段排序方式
@strWhere nvarchar(1000)='', --查询条件
@Iscount bit=0 --返回记录总数，非0则返回
)
as
declare @strsql nvarchar(4000) --主语句
declare @strtmp nvarchar(500) --临时变量
declare @strorder nvarchar(500) --总排序方式
declare @stropporder nvarchar(500) --总排序的反方式
declare @strorderfldlist nvarchar(500) --所有要排序的字段序列
declare @selectfld nvarchar(500) --选择主键page
declare @selectfirst nvarchar(500) --选择主排序page
declare @selectsecond nvarchar(500) --选择副排序page
/**/
declare @strwheretmp nvarchar(500) --临时where变量
if @strwhere!=''
set @strwheretmp=' where '
else
set @strwheretmp=''
/**/
set @strsql=''
set @strtmp=''
set @strorder=''
set @stropporder=''
set @strorderfldlist=''
set @selectfld=''
set @selectfirst=''
set @selectsecond=''
if @pagesize<1
set @pagesize=10
if @pageindex<1
set @pageindex=1
if @fields=''
set @fields='*'
if @FirstfldName=''
set @FirstfldName=''
if @SecondfldName=''
set @SecondfldName=''
--------------------------------bengin
----------总排序方式
if @fldorder!=0
begin
-----正排序
set @strorder=@fldname + ' desc '
if @secondfldname!=''
if @secondfldorder!=0
set @strorder=@secondfldname + ' desc,'+@strorder
else
set @strorder=@secondfldname + ' asc,'+@strorder
if @firstfldname!=''
if @firstfldorder!=0
set @strorder=@firstfldname + ' desc,'+@strorder
else
set @strorder=@firstfldname + ' asc,'+@strorder
-----反排序
set @stropporder=@fldname + ' asc '
if @secondfldname!=''
if @secondfldorder!=0
set @stropporder=@secondfldname + ' asc,'+@stropporder
else
set @stropporder=@secondfldname + ' desc,'+@stropporder
if @firstfldname!=''
if @firstfldorder!=0
set @stropporder=@firstfldname + ' asc,'+@stropporder
else
set @stropporder=@firstfldname + ' desc,'+@stropporder
end
else
begin
-----正排序
set @strorder=@fldname + ' asc '
if @secondfldname!=''
if @secondfldorder!=0
set @strorder=@secondfldname + ' desc,'+@strorder
else
set @strorder=@secondfldname + ' asc,'+@strorder
if @firstfldname!=''
if @firstfldorder!=0
set @strorder=@firstfldname + ' desc,'+@strorder
else
set @strorder=@firstfldname + ' asc,'+@strorder
-----反排序
set @stropporder=@fldname + ' desc '
if @secondfldname!=''
if @secondfldorder!=0
set @stropporder=@secondfldname + ' desc,'+@stropporder
else
set @stropporder=@secondfldname + ' asc,'+@stropporder
if @firstfldname!=''
if @firstfldorder!=0
set @stropporder=@firstfldname + ' desc,'+@stropporder
else
set @stropporder=@firstfldname + ' asc,'+@stropporder
end
set @stropporder=' order by '+@stropporder
set @strorder=' order by '+@strorder +' '
---------总排序方式end
---------总排序字段序列
set @strorderfldlist='id'
if @firstfldname!=''
begin
set @strorderfldlist=@firstfldname+','+@strorderfldlist
if @secondfldname!=''
set @strorderfldlist=@secondfldname+','+@strorderfldlist
end
---------总排序字段序列end
if @Firstfldname!=''
-------若主排序字段不空，则按主排序字段排序，
begin
if @fldorder!=0
set @strTmp = '<=(select top 1'
else
set @strTmp = '>=(select top 1'
set @selectfld=@fldname + replace(@strtmp,'=','')+'('+@fldname+')from(select top ' + str((@PageIndex-1)*@PageSize)
+ ' '+@strorderfldlist +' from '+@tblname+' '+@strwheretmp+@strwhere +@strorder
+')as tbltmp '+ @stropporder +')'
if @firstfldname!=''
set @selectfirst=' and '+@firstfldname + @strtmp+'('+@firstfldname+')from(select top ' + str((@PageIndex-1)*@PageSize)
+ ' '+@strorderfldlist +' from '+@tblname+' '+@strwheretmp+@strwhere +@strorder
+')as tbltmp '+ @stropporder +')'
if @secondfldname!=''
set @selectsecond=' and '+@secondfldname + @strtmp+'('+@secondfldname+')from(select top ' + str((@PageIndex-1)*@PageSize)
+ ' '+@strorderfldlist +' from '+@tblname+' '+@strwheretmp+@strwhere +@strorder
+')as tbltmp '+ @stropporder +')'
------------多字段排序代码
set @strSQL = 'select top ' + str(@PageSize) + ' '+@fields+' from '
+ @tblName + ' where ' + @selectfld + @selectfirst+ @selectsecond+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' '+@fields+' from '
+ @tblName + ' where ' + @selectfld + @selectfirst+ @selectsecond+' and ' + @strWhere + ' ' + @strOrder
------------多字段排序代码end
end
-------若主排序字段不空，则按主排序字段排序，end
else
-------若主排序字段为空，则按主键排序，
begin
if charindex('.',@fldname)>0
set @selectfld=substring(@fldname,charindex('.',@fldname)+1,len(@fldname))
else
set @selectfld=@fldname
if @fldorder!=0
begin
set @strTmp = '<(select top 1'
set @strOrder = ' order by ' + @fldName +' desc'
set @stropporder=' order by '+@selectfld +' asc'
end
else
begin
set @strTmp = '>(select top 1'
set @strOrder = ' order by ' + @fldName +' asc'
set @stropporder=' order by '+@selectfld +' desc '
end
set @strSQL = 'select top ' + str(@PageSize) + ' '+@fields+' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
+ @selectfld + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
+ @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp '+@stropporder +')'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' '+@fields+' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
+ @selectfld + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp '+@stropporder +') and ' + @strWhere + ' ' + @strOrder
end
-------若主排序字段为空，则按主键排序，end
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' '+@fields+' from '
+ @tblName + '' + @strTmp + ' ' + @strOrder
end
if @IsCount != 0
begin
set @strSQL = 'select count(*) as Total from ' + @tblName + ''
if @strWhere!=''
set @strSQL='select count(*) as Total from '+@tblName +' where ' +@strWhere
end
--print @strsql
exec (@strSQL)
GO
