SQL Server stored procedure returns both the paged result set and the total number

Objective

For a long time did not touch the database, the weekend at home to write a report of the stored procedures, a time to use the stored procedures to achieve paging and calculate the total number of records do not know how better to achieve. According to our normal business logic, the stored procedure data is first paged, followed by a number of query criteria, returning the paging result set and returning the total number of records to the client.

My summary of such a business stored procedure is as follows: 1, the kernel layer, usually the field to be queried or the field to be calculated, this part is taken out separately. 2, the query condition layer. If the kernel only queries some fields, the condition can be placed in the query condition layer stitching. If the kernel layer is purely statistical business logic, then the query criteria must be placed on the kernel layer, like our usual sum, GROUPBY business.   3, add paging parameters (that is, we now use most of the row_number to add RN parameters). In the stored procedure, we will generally declare each part of the variable for execution stitching.

Stored Procedures

CREATE proc [dbo]. [Usp_manyidu] (@seatno nvarchar (), @pageIndex int, @pageSize int, @rsCount int out) asbegindeclare @sql nvarchar (max)--Stitching the kernel SQLDECLA      Re @where nvarchar (max) = ' where 1=1 '--query conditional stitching string declare @cols nvarchar (max)--query field, calculated field declare @sort nvarchar (50) --Sort Set @sql = ' from Dbo.log where seatno are not null and seatno<> ' "Group by Seatno ' Set @cols = ' Seatno,sum (case w Hen manyidu=0 then 1 else 0 end) as Manyi, sum (case is manyidu=1 then 1 else 0 end) as Yiban, sum (case whe n manyidu=2 then 1 else 0 end) as Bumanyi, SUM (case if 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 * FR Om (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 @strSQLexec (@ strSQL) Set @strSQL = ' Select @total =count (*) from (select ' [email protected][email protected]+ ') as Tmptable ' [Email protected]print @strSQLexec sp_executesql @strSQL, N ' @total int out ', @[email protected] Outendgo

　　

SQL Server stored procedure returns both the paged result set and the total number