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

MSSQL how to achieve paging storage procedures, I believe that many inexperienced people are helpless about this, this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

The details are as follows:

USE [DB_Common]GO/** **** Object: StoredProcedure [dbo]. [Com_Pagination] Script Date: 03/09/2012 23:46:20 ****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/** *************************************** 0, --Total pages -- 'Person',--table name of query ' Person p LEFT JOIN TE a ON a.PID=p.Id ', --Table name of query (multi-table here) 'a.* ', --query data column 'p. ID', --permutation field 'p. ID', --grouping field 2, --Records per page 1, --Current Page 0, --Whether to use grouping, No Yes 'a.pid =2'--Query criteria ***************************************************************************************************/CREATE PROCEDURE [dbo]. [Com_Pagination]@TotalCount INT OUTPUT, --Total number of records @TotalPage INT OUTPUT, --Total number of pages @Table NVARCHAR(1000), --Table name of query (Multiple tables may be used, for example: Person p LEFT JOIN TE a ON a.PID=p.Id)@Column NVARCHAR(1000), --Query field, multi-column or *@OrderColumn NVARCHAR(100), --Sort field @GroupColumn NVARCHAR(150), --Grouping field @PageSize INT, --Records per page @CurrentPage INT, --Current pages @Group TINYINT, --Whether to use grouping, no Yes @Condition NVARCHAR(4000) --Query condition (Note: If this is a multi-table query, you can also follow the condition here, for example: a.pid=2) ASDECLARE @PageCount INT, --Total pages @strSql NVARCHAR(4000), --main query statement @strTemp NVARCHAR(2000), --temporary variable @strCount NVARCHAR(1000), --statistical statements @strOrderType NVARCHAR(1000) --sort statements BEGINSET @PageCount = @PageSize * (@CurrentPage -1)SET @strOrderType = ' ORDER BY ' + @OrderColumn + 'IF @Condition != ''BEGIN IF @CurrentPage = 1 BEGIN IF @GROUP = 1 BEGIN SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table + ' WHERE ' + @Condition + ' GROUP BY ' + @GroupColumn SET @strCount = @strCount + ' SET @TotalCount=@@ROWCOUNT' SET @strSql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Column + ' FROM ' + @Table + ' WHERE ' + @Condition + ' GROUP BY ' + @GroupColumn + ' ' + @strOrderType END ELSE BEGIN SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table + ' WHERE ' + @Condition SET @strSql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Column + ' FROM ' + @Table + ' WHERE ' + @Condition + ' ' + @strOrderType END END ELSE BEGIN IF @GROUP = 1 BEGIN SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table + ' WHERE ' + @Condition + ' GROUP BY ' + @GroupColumn SET @strCount = @strCount + ' SET @TotalCount=@@ROWCOUNT' SET @strSql = 'SELECT * FROM (SELECT TOP (2000) ' + @Column + ',ROW_NUMBER() OVER(' + @strOrderType + ') AS NUM FROM ' + @Table + ' WHERE ' + @Condition + ' GROUP BY ' + @GroupColumn + ') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) + ' AND ' + STR(@PageCount + @PageSize) END ELSE BEGIN SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table + ' WHERE ' + @Condition SET @strSql = 'SELECT * FROM (SELECT TOP (2000) ' + @Column + ',ROW_NUMBER() OVER(' + @strOrderType + ') AS NUM FROM ' + @Table + ' WHERE ' + @Condition + ') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) + ' AND ' + STR(@PageCount + @PageSize) END ENDENDELSE --No query condition BEGIN IF @CurrentPage = 1 BEGIN IF @GROUP = 1 BEGIN SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table + ' GROUP BY ' + @GroupColumn SET @strCount = @strCount + 'SET @TotalCount=@@ROWCOUNT' SET @strSql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Column + ' FROM ' + @Table + ' GROUP BY ' + @GroupColumn + ' ' + @strOrderType END ELSE BEGIN SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table SET @strSql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Column + ' FROM ' + @Table + ' ' + @strOrderType END END ELSE BEGIN IF @GROUP = 1 BEGIN SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table + ' GROUP BY ' + @GroupColumn SET @strCount = @strCount + 'SET @TotalCount=@@ROWCOUNT' SET @strSql = 'SELECT * FROM (SELECT TOP (2000) ' + @Column + ',ROW_NUMBER() OVER(' + @strOrderType + ') AS NUM FROM ' + @Table + ' GROUP BY ' + @GroupColumn + ') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) + ' AND ' + STR(@PageCount + @PageSize) END ELSE BEGIN SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table SET @strSql = 'SELECT * FROM (SELECT TOP (2000) ' + @Column + ',ROW_NUMBER() OVER(' + @strOrderType + ') AS NUM FROM ' + @Table + ') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) + ' AND ' + STR(@PageCount + @PageSize) END ENDENDEXEC sp_executesql @strCount, N'@TotalCount INT OUTPUT', @TotalCount OUTPUTIF @TotalCount > 2000BEGIN SET @TotalCount = 2000ENDIF @TotalCount%@PageSize = 0BEGIN SET @TotalPage = @TotalCount / @PageSizeENDELSEBEGIN SET @TotalPage = @TotalCount / @PageSize + 1ENDSET NOCOUNT ONEXEC (@strSql)ENDSET NOCOUNT OFF/** EXEC Com_Pagination 100, --Total number of records 0, --Total pages -- 'Person',--table name of query ' Person p LEFT JOIN TE a ON a.PID=p.Id ', --Table name of query (multi-table here) 'a.* ', --query data column 'p. ID', --permutation field 'p. ID', --grouping field 2, --Records per page 1, --Current Page 0, --Whether to use grouping, No Yes 'a.pid =2'--query condition SELECT a.* FROM Person p LEFT JOIN TE a ON a.PID = p.IdWHERE a.pid = 2**/After reading the above, do you know how to implement paging storage procedures in MSSQL? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

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.