Sql Server 分頁預存程序，sql預存程序

Sql Server 分頁預存程序，sql預存程序

分頁預存程序一：

--/*-----預存程序 分頁處理 孫偉 2005-03-28建立 -------*/ --/*----- 對資料進行了2分處理使查詢前半部分資料與查詢後半部分資料效能相同 -------*/ --/*-----預存程序 分頁處理 孫偉 2005-04-21修改 添加Distinct查詢功能-------*/ --/*-----預存程序 分頁處理 孫偉 2005-05-18修改 多欄位定序問題-------*/ --/*-----預存程序 分頁處理 孫偉 2005-06-15修改 多欄位排序修改-------*/ --/*-----預存程序 分頁處理 孫偉 2005-12-13修改 修改資料分頁方式為top max模式效能有極大提高-------*/ --/*-----缺點：相對之前的not in版本主鍵只能是整型欄位,如主鍵為GUID類型請使用not in 模式的版本-------*/ CREATE PROCEDURE dbo.proc_ListPageInt ( @tblName nvarchar(200), ----要顯示的表或多個表的串連 @fldName nvarchar(500) = '*', ----要顯示的欄位列表 @pageSize int = 10, ----每頁顯示的記錄個數 @page int = 1, ----要顯示那一頁的記錄 @pageCount int = 1 output, ----查詢結果分頁後的總頁數 @Counts int = 1 output, ----查詢到的記錄數 @fldSort nvarchar(200) = null, ----排序欄位列表或條件 @Sort bit = 0, ----排序方法，0為升序，1為降序(如果是多欄位排列Sort指代最後一個排序欄位的排列順序(最後一個排序欄位不加排序標記)--程式傳參如：' SortA Asc,SortB Desc,SortC ') @strCondition nvarchar(1000) = null, ----查詢條件,不需where @ID nvarchar(150), ----主表的主鍵 @Dist bit = 0 ----是否添加查詢欄位的 DISTINCT 預設0不添加/1添加 ) 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(*)' 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 @strCondition is null or @strCondition='' --沒有設定顯示條件 begin set @sqlTmp = @fldName + ' From ' + @tblName set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblNameset @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 @tmpCounts int if @Counts = 0 set @tmpCounts = 1 else set @tmpCounts = @Counts --取得分頁總數 set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize /**//**當前頁大於總頁數 取最後一頁**/ if @page>@pageCount set @page=@pageCount --/*-----資料分頁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 --//***顯示分頁 if @strCondition is null or @strCondition='' --沒有設定顯示條件 begin if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分資料處理 begin if @page=1 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName+' order by '+ @fldSort +' '+ @strFSortType else begin set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName+' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(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(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType else set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName+' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(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<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分資料處理 begin if @page=1 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName+' where 1=1 ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType else begin set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName+' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(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(@lastcount as VARCHAR(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(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName+' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(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 ------返回查詢結果----- exec sp_executesql @strTmp --print @strTmp SET NOCOUNT OFF GO 

怎麼在資料庫中測試呢？

declare @pageCount int declare @Counts int exec [dbo].[proc_ListPageInt] 'sysobjects', '* ', 20,1,@pageCount output,@Counts output,'id', 0,'','id',0print @pageCount --這個可有可無 print @Counts --這個可有可無 

執行效果如下：

分頁預存程序二：

USE [JianKunKingTestDatabase001] GO /****** Object: StoredProcedure [dbo].[A_P_HelpPageShow] Script Date: 01/21/2015 19:19:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[A_P_HelpPageShow] ( @tblName nvarchar(max), -- 表名 @strGetFields varchar(1000) = '*', -- 需要返回的列 @fldName varchar(255)='', -- 排序的欄位名 @PageSize int = 10, -- 頁尺寸 @PageIndex int = 1, -- 頁碼 @OrderType bit = 0, -- 設定排序類型, 非 0 值則降序 @strWhere varchar(1500) = '', -- 查詢條件 (注意: 不要加 where) @Counts int = 0 output --查詢到的記錄數 ) AS declare @strSQL nvarchar(4000) -- 主語句 declare @strTmp nvarchar(110) -- 臨時變數 declare @strOrder nvarchar(400) -- 排序類型 declare @totalRecord int --查詢到的記錄數 declare @SqlCounts nvarchar(max) ----對總數查詢進行SQL構造 --計算總記錄數 begin if @strWhere !='' set @SqlCounts = 'select @totalRecord=count(*) from ' + @tblName + ' where '+@strWhereelse set @SqlCounts = 'select @totalRecord=count(*) from ' + @tblName + '' end --print @strWhere --print @SqlCounts exec sp_executesql @SqlCounts,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--計算總記錄數 set @Counts=@totalRecord begin if @OrderType != 0 begin set @strTmp = '<(select min' set @strOrder = ' order by ' + @fldName +' desc' --如果@OrderType不是0，就執行降序，這句很重要！ end else begin set @strTmp = '>(select max' set @strOrder = ' order by ' + @fldName +' asc' end --print @strOrder if @PageIndex = 1 begin if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder else set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' '+ @strOrder --如果是第一頁就執行以上代碼，這樣會加快執行速度 end else begin --以下代碼賦予了@strSQL以真正執行的SQL代碼 set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @fldName + ' ' + @strTmp + '('+ @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' + @tblName + ' ' + @strOrder + ') as tblTmp)'+ @strOrder --print @strSQL if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @fldName + ' ' + @strTmp + '(' + @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder end end --print @strSQL exec sp_executesql @strSQL GO 

怎麼在資料庫中測試呢？

select count(*) from sysobjects ;declare @CountsAA int exec [dbo].[A_P_HelpPageShow] 'sysobjects', '* ', 'id ',20, 1,1, ' ',@CountsAA output print @CountsAA --這個可有可無

執行結果如下：