[MSSQL] 自己寫的SQL分頁預存程序(傳筆數Range版、傳頁數版)

2011-05-22

9645
0
SQL Server
2011-09-29

[SQL] 自己寫的SQL分頁預存程序(傳筆數Range版、傳頁數版)

因為之前用的My Program 利用CTE將分頁資料取出-Stored Procedure篇

有不能傳子查詢的缺點和Select Top 不能work，(因為Replace(@sqlcmd,'select ',' ')，所以乾脆自己改寫

傳筆數Range版(回傳結果資料集和SqlQuery的總筆數)

-- =============================================
-- Author:		Shadow
-- Create date: 2011.5.22
-- Description:	給前端應用程式分頁的預存程序(傳筆數Range版)
-- =============================================
Create PROCEDURE [uSP_pageSQL_passRowNum]
	(@sqlQuery varchar(max), --Sql Query的部份(不含排序)
	 @sqlOrder varchar(max), --Sql排序
	 @startRowNo int --開始筆數 
	 ,@endRowNo int  --結束筆數
	 )
AS
BEGIN
	
	Declare @s varchar(max) --要執行的SQL字串
	Set @s = 
	' with query 
	  AS
	  (
	    Select * ,Row_Number() over ('+@sqlOrder+') as rowNo from ('+@sqlQuery+') subTable /*從query語法編序號*/
      )
        Select * from query Where rowNo Between ' +Convert(varchar(max),@startRowNo) + ' And ' + Convert(varchar(max),@endRowNo)+ ' 
        Select count(*) as TotalRow from ('+@sqlQuery+') subTable' --query的總筆數
         
	--print @s --Debug用
	EXECUTE(@s) --回傳兩個資料集

END

呼叫方法：

傳頁數版(回傳結果資料集和目前頁數、總共頁數、SqlQuery總筆數)

-- =============================================
-- Author:		Shadow
-- Create date: 2011.5.22
-- Description:	給前端應用程式分頁的預存程序(傳頁數版)
-- =============================================
Create PROCEDURE [uSP_pageSQL_passPageNum]
	(@sqlQuery varchar(max),--Sql Query的部份(不含排序)
	 @sqlOrder varchar(max),--Sql排序
	 @currentPage int,--目前在第幾頁
	 @pageSize int)--每頁顯示幾筆資料
AS
BEGIN
	
	Declare @s varchar(max)--要執行的SQL字串
	Set @s = 
	' Declare @TotalRow bigint /*總筆數*/
	  Declare @totalPages bigint /*總頁數*/
	  
	  
	  ;with query 
	  AS
	  (
	    Select * ,Row_Number() over ('+@sqlOrder+') as rowNo from ('+@sqlQuery+') subTable /*從query語法編序號*/
      )
         /*撈資料，從第幾筆到第幾筆*/
         Select * from query Where rowNo Between ' +Convert(varchar(max),(@currentPage*@pageSize)-(@pageSize-1)) +' And ' + Convert(varchar(max),(@currentPage*@pageSize))+
         
         'Select @TotalRow =count(*)  from ('+@sqlQuery+') subTable  ' +--query的總筆數
         'Set @totalPages = (@TotalRow/'+Convert(varchar(max),@pageSize) +')'+
         'if (@TotalRow%'+Convert(varchar(max),@pageSize)+'>0) Set @totalPages=(@totalPages+1) ' + --設定總頁數
         'Select '+Convert(varchar(max),@currentPage)+' AS currentPage,@totalPages AS totalPages,@TotalRow AS TotalRow'
         
         
	--print @s --Debug用
	EXECUTE(@s) --回傳兩個資料集
      
END

呼叫方法：

呼叫方法2

2011.6.2 追加SQL Server 2000的寫法

-- =============================================
-- Author:        Shadow
-- Create date: 2011.6.2
-- Description:    給前端應用程式分頁的預存程序(傳頁數版) 只適用SQL Server 2000
-- =============================================
CREATE PROCEDURE [uSP_pageSQL_passPageNum_onlyForSQL2000]
    (@sqlQuery varchar(max),--Sql Query的部份(不含排序)
     @sqlOrder varchar(max),--Sql排序
     @currentPage int,--目前在第幾頁
     @pageSize int)--每頁顯示幾筆資料
AS
BEGIN
    
    Declare @s varchar(max)--要執行的SQL字串
    Set @s = 
    ' Declare @TotalRow bigint /*總筆數*/
      Declare @totalPages bigint /*總頁數*/
      
      
      Select IDENTITY(int,1,1) As rowNum, * 
      into #tempTable 
      from (' + @sqlQuery + ') subTable '+ 
      @sqlOrder + '
      /*撈資料，從第幾筆到第幾筆*/
      Select * from #tempTable
      Where rowNum Between ' +Convert(varchar(max),(@currentPage*@pageSize)-(@pageSize-1)) +' And ' + Convert(varchar(max),(@currentPage*@pageSize))+
         
         ' Select @TotalRow =count(*)  from ('+@sqlQuery+') subTable  ' +--query的總筆數
         ' Set @totalPages = (@TotalRow/'+Convert(varchar(max),@pageSize) +')'+
         ' if (@TotalRow%'+Convert(varchar(max),@pageSize)+'>0) Set @totalPages=(@totalPages+1) ' + --設定總頁數        
         ' Select '+Convert(varchar(max),@currentPage)+' AS currentPage,@totalPages AS totalPages,@TotalRow AS TotalRow'
         
         
    --print @s --Debug用      
    EXECUTE(@s) --回傳兩個資料集     
END
GO

呼叫方法：

SQL2000

註：SQL Server 2000的若用在SQL 2005 以上，會發生錯誤

訊息 8108，層級 16，狀態 1，行 5
無法使用 SELECT INTO 陳述式來對資料表 '#tempTable' 加入識別欄位，該資料表已經有了繼承識別屬性的資料行 'ProductID'。

以上三個預存程序，目前發現到的限制

如果傳參數「Order by 欄位A」的話
則在select_list中，欄位A就要出現
而且參數不能傳「Order by 資料表別名.欄位A 」
如果select_list出現相同欄位名稱，必須給別名，例如：Select 欄位A,欄位A As 別名欄位A
此時可以傳參數「Order by 別名欄位A」

2011.9.29 其他文章

(使用Cursor)SQL 分頁用預存程序 - 飆汗吉米的點部落

回首頁