Implementation of SQL paging stored procedure code

Time:2021-3-10

SQL paging stored procedure code, need friends directly to use, use is very simple.

The sharing code is as follows

USE [SendMessage]
GO
/****** Object: StoredProcedure [dbo].[pages]  Script Date: 07/09/2015 13:46:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[pages]
(
@TBNAME nvarchar (100), -- the name of the table to be paged
@Fieldkey nvarchar (1000), -- used to locate the primary key (only key) field of a record. It can be multiple fields separated by commas
@Pagecurrent int = 1, - page number to display
@PageSize int = 10, - size per page (number of records)
@Fieldshow nvarchar (1000) = ', - comma separated list of fields to display. If not specified, all fields will be displayed
@Fieldorder nvarchar (1000) = ', - comma separated sorted field list. Desc / ASC can be specified after the field
@Wherestring nvarchar (1000) = n '' -- query condition
)
AS
begin
 
IF ISNULL(@FieldKey,N'')=''
BEGIN
  Raiserror (n 'primary key (or only key) is required for paging processing', 1,16)
  RETURN
END
 
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
 
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
 
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
 
IF ISNULL(@FieldOrder,N'')=N''
  SET @FieldOrder=N''
ELSE
  SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
 
IF ISNULL(@WhereString,N'')=N''
  SET @WhereString=N''
ELSE
  SET @WhereString=N'WHERE '[email protected]+N''
 
--Calculate the topn value of pagination display
DECLARE @TopN varchar(20),@StartRecord varchar(20),@EndRecord varchar(20)
SELECT @[email protected],
  @StartRecord=(@PageCurrent-1)*@PageSize+1,
  @EndRecord=(@PageCurrent-1)*@[email protected]
 
--The first page is displayed directly
IF @PageCurrent=1
  EXEC(N'SELECT TOP '[email protected]
    +N' '[email protected]
    +N' FROM '[email protected]
    +N' '[email protected]
    +N' '[email protected])
ELSE
EXEC(N'with temptbl as(
select ROW_NUMBER() Over('[email protected]+') as row, '[email protected]+' from '[email protected]+N' '[email protected]+')
select '[email protected]+' from (select B.* from (select '[email protected]+' from temptbl where row between '[email protected]+' and '[email protected]+')A left join '[email protected]+' B on A.'[email protected]+'=B.'[email protected]+')C')
 
END

The above is the whole content of this article, I hope to help you learn.