MSSQL paged stored procedure complete example (supports multi table paged storage)

Time:2020-2-12

An example of this article describes the MSSQL paging stored procedure. To share with you for your reference, 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 ON
GO
/************************************************************
 *
 *SQL paging stored procedure (supports multi table paging storage)
 *
 *Call instance:
 Exec com_pagination 100, -- total records
   0, -- total pages
    --'person' -- table name of query
   '
           Person p
           LEFT JOIN TE a
           ON a.PID=p.Id 
          '-- table name of the query (multiple tables here)
   'a. *' -- query data column
   'p.id', -- arrange fields
   'p.id' -- group field
   2, -- records per page
   1, -- current number of pages
   0, -- use grouping, no yes
   'a.pid = 2' -- query condition
 ************************************************************/
CREATE PROCEDURE [dbo].[Com_Pagination]
@Totalcount int output, -- total records
@Totalpage int output, -- total pages
@Table nvarchar (1000), -- table name of query (multiple tables are allowed, for example: person P left join te a on a.pid = p.id)
@Column nvarchar (1000), -- field of query, can be multiple columns or*
@Ordercolumn nvarchar (100), -- sort field
@Groupcolumn nvarchar (150), -- group field
@PageSize int, -- records per page
@CurrentPage int, -- number of current pages
@Group tinyint, -- use group, no yes
@Condition nvarchar (4000) - query condition (Note: if this is a multi table query, you can follow the condition here, for example: a.pid = 2)
AS
Declare @ pagecount int, -- total pages
    @StrSql nvarchar (4000), -- main query statement
    @Strtemp nvarchar (2000), -- temporary variable
    @Strcount nvarchar (1000), -- statistics statement
    @Strordertype nvarchar (1000) -- sort statement
BEGIN
SET @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 @[email protected]@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 @[email protected]@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
  END
END
ELSE
  --No query criteria
BEGIN
  IF @CurrentPage = 1
  BEGIN
    IF @GROUP = 1
    BEGIN
      SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table
        + ' GROUP BY ' + @GroupColumn
      SET @strCount = @strCount + 'SET @[email protected]@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 @[email protected]@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
  END
END
EXEC sp_executesql @strCount,
   N'@TotalCount INT OUTPUT',
   @TotalCount OUTPUT
IF @TotalCount > 2000
BEGIN
  SET @TotalCount = 2000
END
IF @TotalCount%@PageSize = 0
BEGIN
  SET @TotalPage = @TotalCount / @PageSize
END
ELSE
BEGIN
  SET @TotalPage = @TotalCount / @PageSize + 1
END
SET NOCOUNT ON
EXEC (@strSql)
END
SET NOCOUNT OFF
/**Call instance:
Exec com_pagination 100, -- total records
   0, -- total pages
    --'person' -- table name of query
   '
           Person p
           LEFT JOIN TE a
           ON a.PID=p.Id 
          '-- table name of the query (multiple tables here)
   'a. *' -- query data column
   'p.id', -- arrange fields
   'p.id' -- group field
   2, -- records per page
   1, -- current number of pages
   0, -- use grouping, no yes
   'a.pid = 2' -- query condition
SELECT a.* 
FROM  Person p
    LEFT JOIN TE a
      ON a.PID = p.Id
WHERE a.pid = 2
**/

I hope that this article will be helpful to the SQL Server database programming.