Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to return paging result sets and total numbers at the same time in SQL Server stored procedures. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

1. The kernel layer, which is usually the field to be queried or calculated, is taken out separately.

2. Query the condition layer. If the kernel only queries some fields, the conditions can be spliced at the query condition layer. If the kernel layer is entirely statistical business logic, then the query conditions must be placed in the kernel layer, such as SUM and GROUPBY services.

3. Add paging parameters (that is, we mostly use ROW_NUMBER to add rn parameters). In stored procedures, we usually declare variables for each part separately for execution-time splicing.

Stored procedure

CREATE proc [dbo]. [usp_manyidu] (@ seatno nvarchar (30), @ pageIndex int, @ pageSize int @ rsCount int out) asbegin declare @ sql nvarchar (max)-- splicing kernel SQL declare @ where nvarchar (max) = 'where 1q1'-query condition concatenation string declare @ cols nvarchar (max)-- query field, calculated field declare @ sort nvarchar (50)-- sort 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 endGO above is how the paging result set and the total number are returned at the same time in the SQL Server stored procedure shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.