if@Dist=0 begin set@SqlSelect='select ' set@SqlCounts='Count(*)' end else begin set@SqlSelect='select distinct ' set@SqlCounts='Count(DISTINCT '+@ID+')' end
if@Sort=0 begin set@strFSortType=' ASC ' set@strSortType=' DESC ' end else begin set@strFSortType=' DESC ' set@strSortType=' ASC ' end
--------生成查询语句-------- --此处@strTmp为取得查询结果数量的语句 if@strConditionisnullor@strCondition=''--没有设置显示条件 begin set@sqlTmp=@fldName+' From '+@tblName set@strTmp=@SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName set@strID=' From '+@tblName end else begin set@sqlTmp=+@fldName+'From '+@tblName+' where (1>0) '+@strCondition set@strTmp=@SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName+' where (1>0) '+@strCondition set@strID=' From '+@tblName+' where (1>0) '+@strCondition end
----取得查询结果总数量----- exec sp_executesql @strTmp,N'@Counts int out ',@Counts out declare@tmpCountsint if@Counts=0 set@tmpCounts=1 else set@tmpCounts=@Counts
--//***显示分页 if@strConditionisnullor@strCondition=''--没有设置显示条件 begin if@pageIndex<2or@page<=@pageIndex/2+@pageIndex%2--前半部分数据处理 begin if@page=1 set@strTmp=@SqlSelect+' top '+CAST(@pageSizeasVARCHAR(4))+''+@fldName+' from '+@tblName +' order by '+@fldSort+''+@strFSortType else begin set@strTmp=@SqlSelect+' top '+CAST(@pageSizeasVARCHAR(4))+''+@fldName+' from '+@tblName +' where '+@ID+' <(select min('+@ID+') from ('+@SqlSelect+' top '+CAST(@pageSize*(@page-1) asVarchar(20)) +''+@ID+' from '+@tblName +' order by '+@fldSort+''+@strFSortType+') AS TBMinID)' +' order by '+@fldSort+''+@strFSortType end end else begin set@page=@pageIndex-@page+1--后半部分数据处理 if@page<=1--最后一页数据显示 set@strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+CAST(@lastcountasVARCHAR(4))+''+@fldName+' from '+@tblName +' order by '+@fldSort+''+@strSortType+') AS TempTB'+' order by '+@fldSort+''+@strFSortType else set@strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+CAST(@pageSizeasVARCHAR(4))+''+@fldName+' from '+@tblName +' where '+@ID+' >(select max('+@ID+') from('+@SqlSelect+' top '+CAST(@pageSize*(@page-2)+@lastcountasVarchar(20)) +''+@ID+' from '+@tblName +' order by '+@fldSort+''+@strSortType+') AS TBMaxID)' +' order by '+@fldSort+''+@strSortType+') AS TempTB'+' order by '+@fldSort+''+@strFSortType end end
else--有查询条件 begin if@pageIndex<2or@page<=@pageIndex/2+@pageIndex%2--前半部分数据处理 begin if@page=1 set@strTmp=@SqlSelect+' top '+CAST(@pageSizeasVARCHAR(4))+''+@fldName+' from '+@tblName +' where 1=1 '+@strCondition+' order by '+@fldSort+''+@strFSortType else begin set@strTmp=@SqlSelect+' top '+CAST(@pageSizeasVARCHAR(4))+''+@fldName+' from '+@tblName +' where '+@ID+' <(select min('+@ID+') from ('+@SqlSelect+' top '+CAST(@pageSize*(@page-1) asVarchar(20)) +''+@ID+' from '+@tblName +' where (1=1) '+@strCondition+' order by '+@fldSort+''+@strFSortType+') AS TBMinID)' +''+@strCondition+' order by '+@fldSort+''+@strFSortType end end else begin set@page=@pageIndex-@page+1--后半部分数据处理 if@page<=1--最后一页数据显示 set@strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+CAST(@lastcountasVARCHAR(4))+''+@fldName+' from '+@tblName +' where (1=1) '+@strCondition+' order by '+@fldSort+''+@strSortType+') AS TempTB'+' order by '+@fldSort+''+@strFSortType else set@strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+CAST(@pageSizeasVARCHAR(4))+''+@fldName+' from '+@tblName +' where '+@ID+' >(select max('+@ID+') from('+@SqlSelect+' top '+CAST(@pageSize*(@page-2)+@lastcountasVarchar(20)) +''+@ID+' from '+@tblName +' where (1=1) '+@strCondition+' order by '+@fldSort+''+@strSortType+') AS TBMaxID)' +''+@strCondition+' order by '+@fldSort+''+@strSortType+') AS TempTB'+' order by '+@fldSort+''+@strFSortType end end