Collation of paged stored procedures in sqlserver 2000 and 2005 page 1 / 3

Time:2021-5-7

The paging stored procedure of SQL Server 2005 is divided into three versions, one is not optimized, the other is optimized, and the last one supports join. The paging stored procedure of SQL Server 2000 can also run on SQL Server 2005, but its performance is not as good as that of SQL Server 2005.

In the end, I also attached a binary paging stored procedure, which is also easy to use~~

1.SqlServer 2005:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[up_Page2005]
 @Tablename varchar (50), -- table name
 @Fields varchar (5000) = '*' -- field name (all fields are *)
 @Orderfield varchar (5000), -- sort field (required! Support for multiple fields)
 @Sqlwhere varchar (5000) = null, - conditional statement (without where)
 @PageSize int, -- how many records per page
 @PageIndex int = 1, - specifies the current page
 @Totalpage int output -- returns the total number of pages
as
begin

  Begin tran -- start transaction

  Declare @sql nvarchar(4000);
  Declare @totalRecord int;

  --Calculate the total number of records

  if (@SqlWhere='' or @sqlWhere=NULL)
    set @sql = 'select @totalRecord = count(*) from ' + @TableName
  else
    set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere

  EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--Calculate the total number of records

  --Calculate total pages
  select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)

  if (@SqlWhere='' or @sqlWhere=NULL)
    set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
  else
    set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' with(nolock) where ' + @SqlWhere

  --Handle page out of range
  if @PageIndex<=0
    Set @pageIndex = 1

  if @pageIndex>@TotalPage
    Set @pageIndex = @TotalPage

   --Processing start and end points
  Declare @StartRecord int
  Declare @EndRecord int

  set @StartRecord = (@pageIndex-1)*@PageSize + 1
  set @EndRecord = @StartRecord + @pageSize - 1

  --Continue to synthesize SQL statements
  set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
   print @sql
  Exec(@Sql)
  ---------------------------------------------------
  If @@Error <> 0
   Begin
    RollBack Tran
    Return -1
   End
   Else
   Begin
    Commit Tran
    Return @ totalrecord --- returns the total number of records
   End
end

2.Sql Server 2005:

/******Object: Storedprocedure [dbo]_ Page2005v2] script date: 05 / 21 / 2008 11:27:15******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    <Author,,Name>
-- Create date: <Create Date,,>
-- Description:  <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[up_Page2005V2]
  @Tablename varchar (50), -- table name
 @Fields varchar (5000) = '*' -- field name (all fields are *)
 @Orderfield varchar (5000), -- sort field (required! Support for multiple fields)
 @Sqlwhere varchar (5000) = null, - conditional statement (without where)
 @PageSize int, -- how many records per page
 @PageIndex int = 1, - specifies the current page
 @totalRecord int = 0,
 @Totalpage int output -- returns the total number of pages
AS
BEGIN

   Begin tran -- start transaction

  Declare @sql nvarchar(4000);

  if @totalRecord<=0 begin
    --Calculate the total number of records

    if (@SqlWhere='' or @sqlWhere=NULL)
      set @sql = 'select @totalRecord = count(*) from ' + @TableName
    else
      set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere

    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--Calculate the total number of records
  end

  --Calculate total pages
  select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)

  if (@SqlWhere='' or @sqlWhere=NULL)
    set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
  else
    set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' with(nolock) where ' + @SqlWhere

  --Handle page out of range
  if @PageIndex<=0
    Set @pageIndex = 1

  if @pageIndex>@TotalPage
    Set @pageIndex = @TotalPage

   --Processing start and end points
  Declare @StartRecord int
  Declare @EndRecord int

  set @StartRecord = (@pageIndex-1)*@PageSize + 1
  set @EndRecord = @StartRecord + @pageSize - 1

  --Continue to synthesize SQL statements
  set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
   print @sql
  Exec(@Sql)
  ---------------------------------------------------
  If @@Error <> 0
   Begin
    RollBack Tran
    Return -1
   End
   Else
   Begin
    Commit Tran
    Return @ totalrecord --- returns the total number of records
   End
END

GO

3.Sql Server 2005:

/******Object: Storedprocedure [dbo]_ Page2005V2_ Script date: 05 / 21 / 2008 11:27:30******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[up_Page2005V2_Join]
  @Tablename varchar (150), -- table name
 @Fields varchar (5000) = '*' -- field name (all fields are *)
 @Orderfield varchar (5000), -- sort field (required! Support for multiple fields)
 @Sqlwhere varchar (5000) = null, - conditional statement (without where)
 @PageSize int, -- how many records per page
 @PageIndex int = 1, - specifies the current page
 @totalRecord int = 0,
 @Totalpage int output -- returns the total number of pages
AS
BEGIN

   Begin tran -- start transaction

  Declare @sql nvarchar(4000);

  if @totalRecord<=0 begin
    --Calculate the total number of records

    if (@SqlWhere='' or @sqlWhere=NULL)
      set @sql = 'select @totalRecord = count(*) from ' + @TableName
    else
      set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere

    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--Calculate the total number of records
  end

  --Calculate total pages
  select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)

  if (@SqlWhere='' or @sqlWhere=NULL)
    set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
  else
    set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere

  --Handle page out of range
  if @PageIndex<=0
    Set @pageIndex = 1

  if @pageIndex>@TotalPage
    Set @pageIndex = @TotalPage

   --Processing start and end points
  Declare @StartRecord int
  Declare @EndRecord int

  set @StartRecord = (@pageIndex-1)*@PageSize + 1
  set @EndRecord = @StartRecord + @pageSize - 1

  --Continue to synthesize SQL statements
  set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
   print @sql

  Exec(@Sql)
  ---------------------------------------------------
  If @@Error <> 0
   Begin
    RollBack Tran
    Return -1
   End
   Else
   Begin
    Commit Tran
    Return @ totalrecord --- returns the total number of records
   End
END

4.Sql Server 2000:

USE [game]
GO
/******Object: Storedprocedure [dbo]
                            123 read the full text on the next page

Related articles

Latest comments