Full example of MSSQL paging stored procedures (support for multiple-table paging storage) _mssql

This example describes the MSSQL paging stored procedure. Share to everyone for your reference, specific as follows:

Use [Db_common] Go/****** object: StoredProcedure [dbo]. [Com_pagination] Script Date: 03/09/2012 23:46:20 ******/SET ansi_nulls on Go SET quoted_identifier in Go/********************
    * *sql Paging stored procedures (support for multiple-table paging storage) * Call instance: EXEC com_pagination 100,--Total records 0, total number of pages --' person ',--query's table name ' Man P left JOIN TE A on a.pid=p.id ',--query table name ( Here are multiple tables) ' a.* ',--Querying data columns ' p.id ',--arranging fields ' p.id ',--grouping fields 2,--1 records per page--current page 0,--whether to use grouping, no ' a.pid=2 '-check The condition ************************************************************/CREATE PROCEDURE [dbo].  [com_pagination] @TotalCount int output,--Total record number @TotalPage int output, total page @Table NVARCHAR (1000),--Query table name (multiple tables, for example: person P left JOIN TE an on a.pid=p.id) @Column NVARCHAR (1000),--query fields, can be multiple columns, or * @OrderColumn NVARCHAR (100),--Sort fields @GroupColumn NVARCHAR (150),--Grouping fields @PageSize int,--each page records @CurrentPage int,--the current number of pages @Group TINYINT, whether grouping is used, or @Condition NVARCHAR (4000 ) --Query Criteria (NOTE: If this is a multiple-table query, this can also be followed by conditions, such as: a.pid=2) as DECLARE @PageCount INT,-the total number of pages @strSql NVARCHAR (4000),--The main query statement @str Temp NVARCHAR (2000),--Temporary variable @strCount NVARCHAR (1000),--Statistic statement @strOrderType NVARCHAR (1000)--sort statement BEGIN SET @p
  Agecount = @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 ' + @Tabl E + ' WHERE ' + @Condition + ' GROUP by ' + @GroupColumn Set @strCount = @strCount + ' Set @TotalCount =@ @ROW COUNT ' SET @strSql = ' SELECT top ' + STR (@PageSize) + ' + @Column + ' from ' + @Table + ' WHERE ' + @Cond Ition + ' GROUP by ' + @GroupColumn + ' + @strOrderType end ELSE BEGIN SET @strCount = ' Selec  T @TotalCount =count (*) from ' + @Table + ' WHERE ' + @Condition SET @strSql = ' SELECT top ' + STR (@PageSize) 
      + ' + @Column  + ' from ' + @Table + ' WHERE ' + @Condition + ' + @strOrderType end ELSE BEGIN IF @GROUP = 1 is GIN SET @strCount = ' SELECT @TotalCount =count (*) from ' + @Table + ' WHERE ' + @Condition + ' GROUP by ' + @  Groupcolumn Set @strCount = @strCount + ' Set @TotalCount =@ @ROWCOUNT ' Set @strSql = ' SELECT * FROM (select top (Watts) ' + @Column + ', row_number () over (' + @strOrderType + ') as NUM from ' + @Table + ' WHERE ' + @c Ondition + ' GROUP by ' + @GroupColumn + ') as T WHERE NUM BETWEEN ' + STR (@PageCount + 1) + '  and ' + STR (@PageCount + @PageSize) End ELSE is BEGIN SET @strCount = ' SELECT @TotalCount =count (*) from ' + @Table + ' WHERE ' + @Condition SET @strSql = ' SELECT * FROM (select top) ' + @Column + ', RO W_number () over (' + @strOrderType + ') as NUM from ' + @Table + ' where ' + @Condition + ') as T WHERE N UM BETWEEN ' + STR (@PageCount + 1) + ' and ' + STR (@PageCount + @PageSize) end end ELSE-no query condition BEGIN IF @CurrentPage = 1 Begin IF @GROUP = 1 begin SET @strCount = ' SELECT @TotalCount =count (*) from ' + @Table + ' GROUP B Y ' + @GroupColumn Set @strCount = @strCount + ' Set @TotalCount =@ @ROWCOUNT ' Set @strSql = ' SELECT top ' + STR ( @PageSize) + ' + @Column + ' from ' + @Table + ' GROUP by ' + @GroupColumn + ' + @strOrderType E  ND ELSE BEGIN SET @strCount = ' Select @TotalCount =count (*) from ' + @Table SET @strSql = ' Select Top ' + STR (@PageSize) + ' + @Column + ' from ' + @Table + ' + ' + @strOrderType end ' ELSE BEGIN IF @ GROUP = 1 BEGIN SET @strCount = ' SELECT @TotalCount =count (*) from ' + @Table + ' GROUP by ' + @GroupColu MN 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 W
      Here 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) ' + @c Olumn + ', row_number () (' + @strOrderType + ') as num from ' + @Table + ') as T WHERE NUM BETWEEN '
   + STR (@PageCount + 1) + ' and ' + str (@PageCount + @PageSize) End-end EXEC sp_executesql @strCount, N ' @TotalCount INT output ', @TotalCount output IF @TotalCount > @TotalCount = ' End IF ' @TotalCoun  t% @PageSize = 0 Begin Set @TotalPage = @TotalCount/@PageSize end ELSE begin set @TotalPage = @TotalCount/@PageSize + 1 End Set NOCOUNT in EXEC (@strSql) End set NOCOUNT off/** invocation instance: EXEC com_pagination 100, total records 0, total number of pages--' Pe
     Rson ',--query's table name '      Person P left JOIN TE A on a.pid=p.id ',--query table name (multiple tables here) ' a.* ',--Query data column ' P.id '
    ,--Arrange Fields ' p.id ',--Group Field 2,--1 records per page--current page 0,--use grouping, no ' a.pid=2 '--query criteria SELECT a.* from person p

 Left JOIN TE A on a.pid = p.id WHERE a.pid = 2 **/

Hopefully this article will help you with your SQL Server database design.