SQL Server存儲過程同時傳回分頁結果集和總數

前言

      好長時間沒摸資料庫了，周末在家寫了個報表的存儲過程，一時間對使用存儲過程實作分頁的同時并計算出記錄總數不知道怎麼更好的去實作。按照我們正常的業務邏輯，存儲過程資料首先是分頁，其次接受若幹查詢條件，傳回分頁結果集的同時還需要傳回記錄總數給用戶端。

      我對于這樣一個業務存儲過程總結如下：1、核心層，通常也就是要查詢的字段或者要計算的字段，這部分單獨拿出來。  2、查詢條件層。 如果核心隻是查詢一些字段的話，條件可以放在查詢條件層拼接。 如果核心層完全是統計業務邏輯，那麼查詢條件則必須要放在核心層，像我們常用的SUM、GROUPBY 業務。 3、添加分頁參數（也就是我們現在多數用的ROW_NUMBER添加rn參數）。   存儲過程裡我們一般會單獨聲明每個部分的變量用于執行時拼接。

存儲過程

CREATE proc [dbo].[usp_manyidu]
(
	@seatno nvarchar(30),
	@pageIndex int,
	@pageSize int,
	@rsCount int out
)
as
begin
	declare @sql nvarchar(max)       --拼接核心SQL
	declare @where nvarchar(max)=' where 1=1' --查詢條件拼接字元串
	declare @cols nvarchar(max)     --查詢字段、計算字段
	declare @sort nvarchar(50)      --排序
	
	set @sql=' from dbo.log where seatno is not null and seatno<>'''' group by seatno '
	set @cols='seatno,SUM(case when manyidu=0 then 1 else 0 end) as manyi,
			          SUM(case when manyidu=1 then 1 else 0 end) as yiban,
				      SUM(case when manyidu=2 then 1 else 0 end) as bumanyi,
					  SUM(case when manyidu IS null or manyidu='''' then 1 else 0 end) as weipingjia'
	
	set @sort='order by seatno'
	
	if(@seatno <>'')
		set @where+=' and seatno='[email protected]
		
	
	declare @strSQL nvarchar(max)
	
	set @strSQL=N'select * from (select ROW_NUMBER() over('[email protected]+') as tmpid,* from( select * from (select '[email protected][email protected]+') as tmpTable1'[email protected]+') as tmpTable2) as tmpTable3'
				+' where tmpid between '+STR((@pageIndex-1)*@pageSize+1)+' and '+STR(@pageIndex*@pageSize)
	print @strSQL
	exec(@strSQL)	
	
	set @strSQL='select @total=count(*) from (select '[email protected][email protected]+') as tmpTable'[email protected]
	
	print @strSQL
	exec sp_executesql @strSQL,N'@total int out',@[email protected] out
	
		
end
GO