完整SQL分頁預存程序（支援多表聯結）

標籤：bsp   tmp   des   rom   eid   sort   www   alt   last   

http://www.cnblogs.com/andiki/archive/2009/03/24/1420289.html

 

Code
/*********************************************************
* 作 用：資料分頁(完整SQL分頁預存程序（支援多表聯結）)
* 作 者：齊李平
* 建立日期：2009-03-23
* 使用說明：
1、單表調用方法：EXEC proc_DataPagination @tblName = ‘ipa‘,@fldName = ‘*‘,@pageSize =50,@page = 6789,@fldSort = ‘‘,@Sort = 1,@strCondition = ‘‘,@ID = ‘id‘,@Dist = 0,@pageCount = null,@Counts = NULL
2、多表調用方法：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
* 多表聯集查詢使用需注意：1、多表中的主鍵欄位不能為相同的名稱。2、多表中不能允許具有相同名稱的欄位，如果存在相同名稱的欄位你可以使用AS重新命名
*********************************************************/
alter PROCEDURE [dbo].[proc_DataPagination]
(
@tblName nvarchar(200), ----要顯示的表或多個表的串連
@fldName nvarchar(500) = ‘*‘, ----要顯示的欄位列表
@pageSize int = 10, ----每頁顯示的記錄個數
@page int = 1, ----要顯示那一頁的記錄
@fldSort nvarchar(200) = null, ----排序欄位列表或條件
@Sort bit = 0, ----排序方法，1為升序，0為降序(如果是多欄位排列Sort指代最後一個排序欄位的排列順序(最後一個排序欄位不加排序標記)--程式傳參如：‘ SortA Asc,SortB Desc,SortC ‘)
@strCondition nvarchar(1000) = null, ----查詢條件,不需where
@ID nvarchar(150), ----主表的主鍵
@Dist bit = 0, ----是否添加查詢欄位的 DISTINCT 預設0不添加/1添加
@pageCount int = 1 output, ----查詢結果分頁後的總頁數
@Counts int = 1 output ----查詢到的記錄數
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000) ----存放動態產生的SQL語句
Declare @strTmp nvarchar(1000) ----存放取得查詢結果總數的查詢語句
Declare @strID nvarchar(1000) ----存放取得查詢開頭或結尾ID的查詢語句

Declare @strSortType nvarchar(10) ----資料定序A
Declare @strFSortType nvarchar(10) ----資料定序B

Declare @SqlSelect nvarchar(50) ----對含有DISTINCT的查詢進行SQL構造
Declare @SqlCounts nvarchar(50) ----對含有DISTINCT的總數查詢進行SQL構造


if @Dist = 0
begin
set @SqlSelect = ‘select ‘
set @SqlCounts = ‘Count(0)‘
end
else
begin
set @SqlSelect = ‘select distinct ‘
set @SqlCounts = ‘Count(DISTINCT ‘[email protected]+‘)‘
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=‘,‘[email protected]
end
else
begin
set @fldSort=‘ ‘
end

--------產生查詢語句--------
--此處@strTmp為取得查詢結果數量的語句
if @strCondition is null or @strCondition=‘‘ --沒有設定顯示條件
begin
set @sqlTmp = @fldName + ‘ From ‘ + @tblName
set @strTmp = @SqlSelect+‘ @Counts=‘[email protected]+‘ FROM ‘[email protected]
set @strID = ‘ From ‘ + @tblName
end
else
begin
set @sqlTmp = + @fldName + ‘From ‘ + @tblName + ‘ where (1>0) ‘ + @strCondition
set @strTmp = @SqlSelect+‘ @Counts=‘[email protected]+‘ FROM ‘[email protected] + ‘ where (1>0) ‘ + @strCondition
set @strID = ‘ From ‘ + @tblName + ‘ where (1>0) ‘ + @strCondition
end

----取得查詢結果總數量-----
exec sp_executesql @strTmp,N‘@Counts int out ‘,@Counts out
declare @tmpCounts int
if @Counts = 0
set @tmpCounts = 1
else
set @tmpCounts = @Counts

--取得分頁總數
set @pageCount=(@[email protected])/@pageSize

/**//**當前頁大於總頁數 取最後一頁**/
if @page>@pageCount
set @[email protected]

--/*-----資料分頁2分處理-------*/
declare @pageIndex int --總數/頁大小
declare @lastcount int --總數%頁大小

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=‘‘ --沒有設定顯示條件
begin
if @pageIndex<2 or @page<[email protected] / 2 + @pageIndex % 2 --前半部分資料處理
begin
if @page=1
set @[email protected]+‘ top ‘+ CAST(@pageSize as VARCHAR(40))+‘ ‘+ @fldName+‘ from ‘[email protected]
+‘ order by ‘+ @ID+‘ ‘+ @[email protected]
else
begin
set @[email protected]+‘ top ‘+ CAST(@pageSize as VARCHAR(40))+‘ ‘+ @fldName+‘ from ‘[email protected]
+‘ where ‘[email protected]
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 ‘[email protected]
+‘ order by ‘+ @ID+‘ ‘+ @[email protected]+‘) AS TBMinID)‘
+‘ order by ‘+ @ID+‘ ‘+ @[email protected]
end
end
else

begin
set @page = @[email protected]+1 --後半部分資料處理
if @page <= 1 --最後一頁資料顯示
set @[email protected]+‘ * from (‘[email protected]+‘ top ‘+ CAST(@lastcount as VARCHAR(40))+‘ ‘+ @fldName+‘ from ‘[email protected]
+‘ order by ‘+ @ID +‘ ‘+ @[email protected]+‘) AS TempTB‘+‘ order by ‘+ @ID+‘ ‘+ @[email protected]
else
begin
set @[email protected]+‘ * from (‘[email protected]+‘ top ‘+ CAST(@pageSize as VARCHAR(40))+‘ ‘+ @fldName+‘ from ‘[email protected]
+‘ where ‘[email protected]
if @Sort=0
set @[email protected]+‘ <(select min(‘
else
set @[email protected]+‘ >(select max(‘
set @[email protected]+ @ID +‘) from(‘+ @SqlSelect+‘ top ‘+ CAST(@pageSize*(@page-2)[email protected] as Varchar(20)) +‘ ‘+ @ID +‘ from ‘[email protected]
+‘ order by ‘+ @ID +‘ ‘+ @[email protected]+‘) AS TBMaxID)‘
+‘ order by ‘+ @ID +‘ ‘+ @[email protected]+‘) AS TempTB‘+‘ order by ‘+ @ID+‘ ‘+ @[email protected]
end
end

end

else --有查詢條件
begin
if @pageIndex<2 or @page<[email protected] / 2 + @pageIndex % 2 --前半部分資料處理
begin
if @page=1
set @[email protected]+‘ top ‘+ CAST(@pageSize as VARCHAR(40))+‘ ‘+ @fldName+‘ from ‘[email protected]
+‘ where 1=1 ‘ + @strCondition + ‘ order by ‘+ @ID+‘ ‘+ @[email protected]
else
begin
set @[email protected]+‘ top ‘+ CAST(@pageSize as VARCHAR(40))+‘ ‘+ @fldName+‘ from ‘[email protected]
+‘ where ‘[email protected]
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 ‘[email protected]
+‘ where (1=1) ‘ + @strCondition +‘ order by ‘+ @ID+‘ ‘+ @[email protected]+‘) AS TBMinID)‘
+‘ ‘+ @strCondition +‘ order by ‘+ @ID+‘ ‘+ @[email protected]
end
end
else
begin
set @page = @[email protected]+1 --後半部分資料處理
if @page <= 1 --最後一頁資料顯示
set @[email protected]+‘ * from (‘[email protected]+‘ top ‘+ CAST(@lastcount as VARCHAR(40))+‘ ‘+ @fldName+‘ from ‘[email protected]
+‘ where (1=1) ‘+ @strCondition +‘ order by ‘+ @ID +‘ ‘+ @[email protected]+‘) AS TempTB‘+‘ order by ‘+ @ID+‘ ‘+ @[email protected]
else
begin
set @[email protected]+‘ * from (‘[email protected]+‘ top ‘+ CAST(@pageSize as VARCHAR(40))+‘ ‘+ @fldName+‘ from ‘[email protected]
+‘ where ‘[email protected]
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)[email protected] as Varchar(20)) +‘ ‘+ @ID +‘ from ‘[email protected]
+‘ where (1=1) ‘+ @strCondition +‘ order by ‘+ @ID +‘ ‘+ @[email protected]+‘) AS TBMaxID)‘
+‘ ‘+ @strCondition+‘ order by ‘+ @ID +‘ ‘+ @[email protected]+‘) AS TempTB‘+‘ order by ‘+ @ID+‘ ‘+ @[email protected]
end
end

end

------返回查詢結果-----
SET NOCOUNT off
exec sp_executesql @strTmp
print @strTmp

完整SQL分頁預存程序（支援多表聯結）