完整SQL分页存储过程（支持多表联接）
生活随笔
收集整理的這篇文章主要介紹了
完整SQL分页存储过程（支持多表联接）
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
Code
/********************************************************* ?
* 作??? 用：數(shù)據(jù)分頁(完整SQL分頁存儲過程（支持多表聯(lián)接）)
* 作??? 者：齊李平
* 創(chuàng)建日期：2009-03-23
* 使用說明：
??? 1、單表調(diào)用方法：EXEC proc_DataPagination @tblName = 'ipa',@fldName = '*',@pageSize =50,@page = 6789,@fldSort = '',@Sort = 1,@strCondition = '',@ID = 'id',@Dist = 0,@pageCount = null,@Counts = NULL
??? 2、多表調(diào)用方法：EXEC proc_DataPagination @tblName = 'Info LEFT JOIN InfoType it ON it.typeid=Info.tid',@fldName = 'info.*,it.tname',@pageSize =50,@page = 1806,@fldSort = '',@Sort = 0,@strCondition = '',@ID = 'id',@Dist = 0,??? @pageCount = null,@Counts = NULL?? ?
* 多表聯(lián)合查詢使用需注意：1、多表中的主鍵字段不能為相同的名稱。2、多表中不能允許具有相同名稱的字段，如果存在相同名稱的字段你可以使用AS重命名
*********************************************************/ ?
?alter PROCEDURE [dbo].[proc_DataPagination] ?
?( ?
?@tblName???? nvarchar(200),??????? ----要顯示的表或多個表的連接 ?
?@fldName???? nvarchar(500) = '*',??? ----要顯示的字段列表 ?
?@pageSize??? int = 10,??????? ----每頁顯示的記錄個數(shù) ?
?@page??????? int = 1,??????? ----要顯示那一頁的記錄 ?
?@fldSort??? nvarchar(200) = null,??? ----排序字段列表或條件 ?
?@Sort??????? bit = 0,??????? ----排序方法，1為升序，0為降序(如果是多字段排列Sort指代最后一個排序字段的排列順序(最后一個排序字段不加排序標(biāo)記)--程序傳參如：' SortA Asc,SortB Desc,SortC ') ?
?@strCondition??? nvarchar(1000) = null,??? ----查詢條件,不需where ?
?@ID??????? nvarchar(150),??????? ----主表的主鍵 ?
?@Dist????? bit = 0,?????????? ----是否添加查詢字段的 DISTINCT 默認(rèn)0不添加/1添加 ?
?@pageCount??? int = 1 output,??????????? ----查詢結(jié)果分頁后的總頁數(shù) ?
?@Counts??? int = 1 output??????????????? ----查詢到的記錄數(shù) ?
?) ?
?AS ?
?SET NOCOUNT ON ?
?Declare @sqlTmp nvarchar(1000)??????? ----存放動態(tài)生成的SQL語句 ?
?Declare @strTmp nvarchar(1000)??????? ----存放取得查詢結(jié)果總數(shù)的查詢語句 ?
?Declare @strID???? nvarchar(1000)??????? ----存放取得查詢開頭或結(jié)尾ID的查詢語句 ?
? ?
?Declare @strSortType nvarchar(10)??? ----數(shù)據(jù)排序規(guī)則A ?
?Declare @strFSortType nvarchar(10)??? ----數(shù)據(jù)排序規(guī)則B ?
? ?
?Declare @SqlSelect nvarchar(50)???????? ----對含有DISTINCT的查詢進(jìn)行SQL構(gòu)造 ?
?Declare @SqlCounts nvarchar(50)????????? ----對含有DISTINCT的總數(shù)查詢進(jìn)行SQL構(gòu)造 ?
? ?
? ?
?if @Dist? = 0 ?
?begin ?
???? set @SqlSelect = 'select ' ?
???? set @SqlCounts = 'Count(0)' ?
?end ?
?else ?
?begin ?
???? set @SqlSelect = 'select distinct ' ?
???? set @SqlCounts = 'Count(DISTINCT '+@ID+')' ?
?end ?
? ?
? ?
?if @Sort=0 ?
?begin ?
???? set @strFSortType=' DESC ' ?
???? set @strSortType=' DESC ' ?
?end ?
?else ?
?begin ?
???? set @strFSortType=' ASC ' ?
???? set @strSortType=' ASC ' ?
?end ?
? ?
if(@fldSort is not null and @fldSort<>'')
begin
??? set @fldSort=','+@fldSort
end
else
begin
??? set @fldSort=' '
end
? ?
?--------生成查詢語句-------- ?
?--此處@strTmp為取得查詢結(jié)果數(shù)量的語句 ?
?if @strCondition is null or @strCondition=''???? --沒有設(shè)置顯示條件 ?
?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 ?
? ?
?----取得查詢結(jié)果總數(shù)量----- ?
?exec sp_executesql @strTmp,N'@Counts int out ',@Counts out ?
?declare @tmpCounts int ?
?if @Counts = 0 ?
???? set @tmpCounts = 1 ?
?else ?
???? set @tmpCounts = @Counts ?
? ?
???? --取得分頁總數(shù) ?
???? set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize ?
? ?
???? /**//**當(dāng)前頁大于總頁數(shù) 取最后一頁**/ ?
???? if @page>@pageCount ?
???????? set @page=@pageCount ?
? ?
???? --/*-----數(shù)據(jù)分頁2分處理-------*/ ?
???? declare @pageIndex int --總數(shù)/頁大小 ?
???? declare @lastcount int --總數(shù)%頁大小? ?
? ?
???? set @pageIndex = @tmpCounts/@pageSize ?
???? set @lastcount = @tmpCounts%@pageSize ?
???? if @lastcount > 0 ?
???????? set @pageIndex = @pageIndex + 1 ?
???? else ?
???????? set @lastcount = @pagesize ?
?
?--為配合顯示 ?
?--set nocount off ?
?--select @page curpage,@pageSize pagesize,@pageCount countpage,@tmpCounts [Rowcount] ?
?--set nocount on ?
?
? --//***顯示分頁 ?
???? if @strCondition is null or @strCondition=''???? --沒有設(shè)置顯示條件 ?
???? begin ?
???????? if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2?? --前半部分?jǐn)?shù)據(jù)處理 ?
???????????? begin? ?
???????????????? if @page=1 ?
???????????????????? set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName???????????????????????? ?
???????????????????????? +' order by '+ @ID+' '+ @strFSortType+@fldSort
???????????????? else ?
???????????????? begin???????????????????? ?
???????????????????? set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName ?
???????????????????????? +' where '+@ID ?
???????????????????? if @Sort=0 ?
??????????????????????? set @strTmp = @strTmp + '>(select max(' ?
???????????????????? else ?
??????????????????????? set @strTmp = @strTmp + '<(select min(' ?
???????????????????? set @strTmp = @strTmp + @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName ?
???????????????????????? +' order by '+ @ID+' '+ @strFSortType+@fldSort+') AS TBMinID)' ?
???????????????????????? +' order by '+ @ID+' '+ @strFSortType+@fldSort
???????????????? end???? ?
???????????? end ?
???????? else ?
????????????? ?
???????????? begin ?
???????????? set @page = @pageIndex-@page+1 --后半部分?jǐn)?shù)據(jù)處理 ?
???????????????? if @page <= 1 --最后一頁數(shù)據(jù)顯示???????????? ?
???????????????????? set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(40))+' '+ @fldName+' from '+@tblName ?
???????????????????????? +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort
???????????????? else ?
???????????????????? begin ?
???????????????????? set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName ?
???????????????????????? +' where '+@ID ?
???????????????????????? if @Sort=0 ?
??????????????????????????? set @strTmp=@strTmp+' <(select min(' ?
???????????????????????? else ?
??????????????????????????? set @strTmp=@strTmp+' >(select max(' ?
? set @strTmp=@strTmp+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName ?
???????????????????????? +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TBMaxID)' ?
???????????????????????? +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort
??????????????????? end ?
???????????? end ?
? ?
???? end ?
? ?
???? else --有查詢條件 ?
???? begin ?
???????? if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2?? --前半部分?jǐn)?shù)據(jù)處理 ?
???????? begin ?
???????????????? if @page=1 ?
???????????????????? set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName???????????????????????? ?
???????????????????????? +' where 1=1 ' + @strCondition + ' order by '+ @ID+' '+ @strFSortType+@fldSort
???????????????? else ?
???????????????? begin???????????????????? ?
???????????????????? set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName ?
???????????????????????? +' where '+@ID ?
???????????????????? if @Sort=0 ?
??????????????????????? set @strTmp = @strTmp + '>(select max(' ?
???????????????????? else ?
??????????????????????? set @strTmp = @strTmp + '<(select min(' ?
? ?
????????????????? set @strTmp = @strTmp + @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName ?
???????????????????????? +' where (1=1) ' + @strCondition +' order by '+ @ID+' '+ @strFSortType+@fldSort+') AS TBMinID)' ?
???????????????????????? +' '+ @strCondition +' order by '+ @ID+' '+ @strFSortType+@fldSort
???????????????? end???????????? ?
???????? end ?
???????? else ?
???????? begin? ?
???????????? set @page = @pageIndex-@page+1 --后半部分?jǐn)?shù)據(jù)處理 ?
???????????? if @page <= 1 --最后一頁數(shù)據(jù)顯示 ?
???????????????????? set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(40))+' '+ @fldName+' from '+@tblName ?
???????????????????????? +' where (1=1) '+ @strCondition +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort??????????????????? ?
???????????? else ?
?????????????????? begin ?
???????????????????? set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName ?
???????????????????????? +' where '+@ID ?
???????????????????? if @Sort=0 ?
??????????????????????? set @strTmp = @strTmp + '<(select min(' ?
???????????????????? else ?
??????????????????????? set @strTmp = @strTmp + '>(select max(' ?
??????????????? set @strTmp = @strTmp + @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName ?
???????????????????????? +' where (1=1) '+ @strCondition +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TBMaxID)' ?
???????????????????????? +' '+ @strCondition+' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort ?
????????????????? end?????????????? ?
???????? end???? ?
??? ?
???? end ?
? ?
?------返回查詢結(jié)果----- ?
SET NOCOUNT off ?
?exec sp_executesql @strTmp ?
print @strTmp
/********************************************************* ?
* 作??? 用：數(shù)據(jù)分頁(完整SQL分頁存儲過程（支持多表聯(lián)接）)
* 作??? 者：齊李平
* 創(chuàng)建日期：2009-03-23
* 使用說明：
??? 1、單表調(diào)用方法：EXEC proc_DataPagination @tblName = 'ipa',@fldName = '*',@pageSize =50,@page = 6789,@fldSort = '',@Sort = 1,@strCondition = '',@ID = 'id',@Dist = 0,@pageCount = null,@Counts = NULL
??? 2、多表調(diào)用方法：EXEC proc_DataPagination @tblName = 'Info LEFT JOIN InfoType it ON it.typeid=Info.tid',@fldName = 'info.*,it.tname',@pageSize =50,@page = 1806,@fldSort = '',@Sort = 0,@strCondition = '',@ID = 'id',@Dist = 0,??? @pageCount = null,@Counts = NULL?? ?
* 多表聯(lián)合查詢使用需注意：1、多表中的主鍵字段不能為相同的名稱。2、多表中不能允許具有相同名稱的字段，如果存在相同名稱的字段你可以使用AS重命名
*********************************************************/ ?
?alter PROCEDURE [dbo].[proc_DataPagination] ?
?( ?
?@tblName???? nvarchar(200),??????? ----要顯示的表或多個表的連接 ?
?@fldName???? nvarchar(500) = '*',??? ----要顯示的字段列表 ?
?@pageSize??? int = 10,??????? ----每頁顯示的記錄個數(shù) ?
?@page??????? int = 1,??????? ----要顯示那一頁的記錄 ?
?@fldSort??? nvarchar(200) = null,??? ----排序字段列表或條件 ?
?@Sort??????? bit = 0,??????? ----排序方法，1為升序，0為降序(如果是多字段排列Sort指代最后一個排序字段的排列順序(最后一個排序字段不加排序標(biāo)記)--程序傳參如：' SortA Asc,SortB Desc,SortC ') ?
?@strCondition??? nvarchar(1000) = null,??? ----查詢條件,不需where ?
?@ID??????? nvarchar(150),??????? ----主表的主鍵 ?
?@Dist????? bit = 0,?????????? ----是否添加查詢字段的 DISTINCT 默認(rèn)0不添加/1添加 ?
?@pageCount??? int = 1 output,??????????? ----查詢結(jié)果分頁后的總頁數(shù) ?
?@Counts??? int = 1 output??????????????? ----查詢到的記錄數(shù) ?
?) ?
?AS ?
?SET NOCOUNT ON ?
?Declare @sqlTmp nvarchar(1000)??????? ----存放動態(tài)生成的SQL語句 ?
?Declare @strTmp nvarchar(1000)??????? ----存放取得查詢結(jié)果總數(shù)的查詢語句 ?
?Declare @strID???? nvarchar(1000)??????? ----存放取得查詢開頭或結(jié)尾ID的查詢語句 ?
? ?
?Declare @strSortType nvarchar(10)??? ----數(shù)據(jù)排序規(guī)則A ?
?Declare @strFSortType nvarchar(10)??? ----數(shù)據(jù)排序規(guī)則B ?
? ?
?Declare @SqlSelect nvarchar(50)???????? ----對含有DISTINCT的查詢進(jìn)行SQL構(gòu)造 ?
?Declare @SqlCounts nvarchar(50)????????? ----對含有DISTINCT的總數(shù)查詢進(jìn)行SQL構(gòu)造 ?
? ?
? ?
?if @Dist? = 0 ?
?begin ?
???? set @SqlSelect = 'select ' ?
???? set @SqlCounts = 'Count(0)' ?
?end ?
?else ?
?begin ?
???? set @SqlSelect = 'select distinct ' ?
???? set @SqlCounts = 'Count(DISTINCT '+@ID+')' ?
?end ?
? ?
? ?
?if @Sort=0 ?
?begin ?
???? set @strFSortType=' DESC ' ?
???? set @strSortType=' DESC ' ?
?end ?
?else ?
?begin ?
???? set @strFSortType=' ASC ' ?
???? set @strSortType=' ASC ' ?
?end ?
? ?
if(@fldSort is not null and @fldSort<>'')
begin
??? set @fldSort=','+@fldSort
end
else
begin
??? set @fldSort=' '
end
? ?
?--------生成查詢語句-------- ?
?--此處@strTmp為取得查詢結(jié)果數(shù)量的語句 ?
?if @strCondition is null or @strCondition=''???? --沒有設(shè)置顯示條件 ?
?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 ?
? ?
?----取得查詢結(jié)果總數(shù)量----- ?
?exec sp_executesql @strTmp,N'@Counts int out ',@Counts out ?
?declare @tmpCounts int ?
?if @Counts = 0 ?
???? set @tmpCounts = 1 ?
?else ?
???? set @tmpCounts = @Counts ?
? ?
???? --取得分頁總數(shù) ?
???? set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize ?
? ?
???? /**//**當(dāng)前頁大于總頁數(shù) 取最后一頁**/ ?
???? if @page>@pageCount ?
???????? set @page=@pageCount ?
? ?
???? --/*-----數(shù)據(jù)分頁2分處理-------*/ ?
???? declare @pageIndex int --總數(shù)/頁大小 ?
???? declare @lastcount int --總數(shù)%頁大小? ?
? ?
???? set @pageIndex = @tmpCounts/@pageSize ?
???? set @lastcount = @tmpCounts%@pageSize ?
???? if @lastcount > 0 ?
???????? set @pageIndex = @pageIndex + 1 ?
???? else ?
???????? set @lastcount = @pagesize ?
?
?--為配合顯示 ?
?--set nocount off ?
?--select @page curpage,@pageSize pagesize,@pageCount countpage,@tmpCounts [Rowcount] ?
?--set nocount on ?
?
? --//***顯示分頁 ?
???? if @strCondition is null or @strCondition=''???? --沒有設(shè)置顯示條件 ?
???? begin ?
???????? if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2?? --前半部分?jǐn)?shù)據(jù)處理 ?
???????????? begin? ?
???????????????? if @page=1 ?
???????????????????? set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName???????????????????????? ?
???????????????????????? +' order by '+ @ID+' '+ @strFSortType+@fldSort
???????????????? else ?
???????????????? begin???????????????????? ?
???????????????????? set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName ?
???????????????????????? +' where '+@ID ?
???????????????????? if @Sort=0 ?
??????????????????????? set @strTmp = @strTmp + '>(select max(' ?
???????????????????? else ?
??????????????????????? set @strTmp = @strTmp + '<(select min(' ?
???????????????????? set @strTmp = @strTmp + @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName ?
???????????????????????? +' order by '+ @ID+' '+ @strFSortType+@fldSort+') AS TBMinID)' ?
???????????????????????? +' order by '+ @ID+' '+ @strFSortType+@fldSort
???????????????? end???? ?
???????????? end ?
???????? else ?
????????????? ?
???????????? begin ?
???????????? set @page = @pageIndex-@page+1 --后半部分?jǐn)?shù)據(jù)處理 ?
???????????????? if @page <= 1 --最后一頁數(shù)據(jù)顯示???????????? ?
???????????????????? set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(40))+' '+ @fldName+' from '+@tblName ?
???????????????????????? +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort
???????????????? else ?
???????????????????? begin ?
???????????????????? set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName ?
???????????????????????? +' where '+@ID ?
???????????????????????? if @Sort=0 ?
??????????????????????????? set @strTmp=@strTmp+' <(select min(' ?
???????????????????????? else ?
??????????????????????????? set @strTmp=@strTmp+' >(select max(' ?
? set @strTmp=@strTmp+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName ?
???????????????????????? +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TBMaxID)' ?
???????????????????????? +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort
??????????????????? end ?
???????????? end ?
? ?
???? end ?
? ?
???? else --有查詢條件 ?
???? begin ?
???????? if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2?? --前半部分?jǐn)?shù)據(jù)處理 ?
???????? begin ?
???????????????? if @page=1 ?
???????????????????? set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName???????????????????????? ?
???????????????????????? +' where 1=1 ' + @strCondition + ' order by '+ @ID+' '+ @strFSortType+@fldSort
???????????????? else ?
???????????????? begin???????????????????? ?
???????????????????? set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName ?
???????????????????????? +' where '+@ID ?
???????????????????? if @Sort=0 ?
??????????????????????? set @strTmp = @strTmp + '>(select max(' ?
???????????????????? else ?
??????????????????????? set @strTmp = @strTmp + '<(select min(' ?
? ?
????????????????? set @strTmp = @strTmp + @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName ?
???????????????????????? +' where (1=1) ' + @strCondition +' order by '+ @ID+' '+ @strFSortType+@fldSort+') AS TBMinID)' ?
???????????????????????? +' '+ @strCondition +' order by '+ @ID+' '+ @strFSortType+@fldSort
???????????????? end???????????? ?
???????? end ?
???????? else ?
???????? begin? ?
???????????? set @page = @pageIndex-@page+1 --后半部分?jǐn)?shù)據(jù)處理 ?
???????????? if @page <= 1 --最后一頁數(shù)據(jù)顯示 ?
???????????????????? set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(40))+' '+ @fldName+' from '+@tblName ?
???????????????????????? +' where (1=1) '+ @strCondition +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort??????????????????? ?
???????????? else ?
?????????????????? begin ?
???????????????????? set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName ?
???????????????????????? +' where '+@ID ?
???????????????????? if @Sort=0 ?
??????????????????????? set @strTmp = @strTmp + '<(select min(' ?
???????????????????? else ?
??????????????????????? set @strTmp = @strTmp + '>(select max(' ?
??????????????? set @strTmp = @strTmp + @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName ?
???????????????????????? +' where (1=1) '+ @strCondition +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TBMaxID)' ?
???????????????????????? +' '+ @strCondition+' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort ?
????????????????? end?????????????? ?
???????? end???? ?
??? ?
???? end ?
? ?
?------返回查詢結(jié)果----- ?
SET NOCOUNT off ?
?exec sp_executesql @strTmp ?
print @strTmp
總結(jié)
以上是生活随笔為你收集整理的完整SQL分页存储过程（支持多表联接）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 用rapid-framework开源工具
- 下一篇： 浅谈redis数据库的键值设计