前言 好長時間沒摸資料庫了，周末在家寫了個報表的存儲過程，一時間對使用存儲過程實現分頁的同時並計算出記錄總數不知道怎麼更好的去實現。按照我們正常的業務邏輯，存儲過程數據首先是分頁，其次接受若幹查詢條件，返回分頁結果集的同時還需要返回記錄總數給客戶端。 我對於這樣一個業務存儲過程總結如下：1、內核層， ...
前言
好長時間沒摸資料庫了，周末在家寫了個報表的存儲過程，一時間對使用存儲過程實現分頁的同時並計算出記錄總數不知道怎麼更好的去實現。按照我們正常的業務邏輯，存儲過程數據首先是分頁，其次接受若幹查詢條件，返回分頁結果集的同時還需要返回記錄總數給客戶端。
我對於這樣一個業務存儲過程總結如下：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='+@seatno
declare @strSQL nvarchar(max)
set @strSQL=N'select * from (select ROW_NUMBER() over('+@sort+') as tmpid,* from( select * from (select '+@cols+@sql+') as tmpTable1'+@where+') 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 '+@cols+@sql+') as tmpTable'+@where
print @strSQL
exec sp_executesql @strSQL,N'@total int out',@total=@rsCount out
end
GO
