高效分页sql存储过程(二)

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