SQL Server 2000, 2005 paging storage process finishing 1th/3 page _mssql

The paging stored procedure for SQL Server 2005 is divided into 3 versions, one that has not been optimized, one that is optimized, the last to support join, the SQL Server 2000 paging stored procedure, or run on SQL Server 2005, but the performance is not SQL The version of Server 2005 is good.

In the end I also came with a binary method of paging stored procedures, but also very easy to say AH ~ ~

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 fields (must! support multiple fields) @sqlWhere varchar (5000) = Null,--conditional statement (without where) @pageSize int,--How many records per page @pageIndex int = 1
  ,--Specifies the current number of pages @TotalPage int output--Returns the total number of pages as begin Tran-start transaction Declare @sql nvarchar (4000);

  Declare @totalRecord int;
    --Calculates 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_execute SQL @sql, N ' @totalRecord int OUTPUT ', @totalRecord output--total Record Count--Calculates the total number of pages select @TotalPage =ceiling ((@totalRecord +0.0)/ @PageSize) if (@SqlWhere = ' or @sqlWhere =null) Set @sql = ' SELECT * FROM (select Row_number () over () + @Or Derfield + ') as rowId, ' + @Fields + ' from ' + @TabLename Else Set @sql = ' SELECT * FROM (select Row_number () + @OrderField + ') as rowId, ' + @Fields +

  ' From ' + @TableName + ' with (nolock) where ' + @SqlWhere--handling pages exceeding range if @PageIndex <=0 Set @pageIndex = 1 If @pageIndex > @TotalPage Set @pageIndex = @TotalPage-handles start and end points Declare @StartRecord int Declare @EndRec
  Ord 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), @StartRecord) + ' and ' + CONVERT (varchar), @End
    Record) print @sql Exec (@Sql)---------------------------------------------------If @ @Error <> 0 Begin

 RollBack Tran Return-1 End Else Begin Commit Tran return @totalRecord---returns total records end

2.SQL Server 2005:

/****** object: StoredProcedure [dbo]. [Up_page2005v2] Script Date: 05/21/2008 11:27:15 ******/SET ansi_nulls on Go Set QUOTED_IDENTIFIER in Go--=================== ==========================--Author: <Author,,Name>--Create Date: <create date,,>--Description: <d Escription,,>--============================================= CREATE PROCEDURE [dbo].     [Up_page2005v2] @TableName varchar (50),--table name @Fields varchar (5000) = ' * ',--field name (all fields are *) @OrderField varchar (5000),  --Sort fields (must! support multiple fields) @sqlWhere varchar (5000) = Null,--conditional statement (without adding 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 a transaction Declare

  @sql nvarchar (4000); If @totalRecord <=0 Begin--Calculates 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--calculates the total record number end--Calculates the total  Pages Select @TotalPage =ceiling ((@totalRecord +0.0)/@PageSize) if (@SqlWhere = ' or @sqlWhere =null) Set @sql = ' SELECT ' * FROM (select Row_number () "+ @OrderField +") as RowId, ' + @Fields + ' from ' + @TableName else se T @sql = ' SELECT * FROM (select Row_number () + @OrderField + ") as RowId, ' + @Fields + ' from ' + @TableNa Me + ' with (nolock) where ' + @SqlWhere--processing pages exceeding range if @PageIndex <=0 Set @pageIndex = 1 if @pageIndex >@ Totalpage Set @pageIndex = @TotalPage--handles start and end points Declare @StartRecord int Declare @EndRecord int Set @Star Trecord = (@pageIndex-1) * @PageSize + 1 Set @EndRecord = @StartRecord + @pageSize-1--continue to synthesize the SQL statement set @Sql = @Sql + ' As T where rowId between ' + convert (varchar, @StartRecord) + ' and ' + CONVERT (varchar), @EndRecord) print @sq L Exec (@Sql)---------------------------------------------------If @ @Error <> 0 Begin RollBack Tran Return-1 End El

 Se Begin Commit Tran return @totalRecord---Returns total records end of Go

3.SQL Server 2005:

/****** object: StoredProcedure [dbo]. [Up_page2005v2_join] Script Date: 05/21/2008 11:27:30 ******/SET ansi_nulls on Go SET quoted_identifier in Go CREATE PROCEDURE [dbo]. [Up_page2005v2_join] @TableName varchar (150),--table name @Fields varchar (5000) = ' * ',--field name (all fields are *) @OrderField varchar (  5000),--Sort fields (must! support multiple fields) @sqlWhere varchar (5000) = Null,--conditional statement (without adding 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 De

  Clare @sql nvarchar (4000); If @totalRecord <=0 Begin--Calculates 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--compute Total Records End--Calculates the total number of pages select @To Talpage=ceiling ((@totalRecord +0.0)/@PageSize) if (@SqlWhere = ' or @sqlWhEre=null) Set @sql = ' SELECT * FROM (select Row_number () + @OrderField + ') as rowId, ' + @Fields + ' F  Rom ' + @TableName else set @sql = ' SELECT * FROM (select Row_number () over (order by ' + @OrderField + ') as RowId, '

  + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere--handling pages exceeding range if @PageIndex <=0 Set @pageIndex = 1 If @pageIndex > @TotalPage Set @pageIndex = @TotalPage-handles start and end points Declare @StartRecord int Declare @EndReco
  rd 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), @StartRecord) + ' and ' + CONVERT (varchar), @EndR
    Ecord) Print @sql Exec (@Sql)---------------------------------------------------If @ @Error <> 0 Begin

 RollBack Tran Return-1 End Else Begin Commit Tran return @totalRecord---returns total records end

4.SQL Server 2000:

Use [game]
go
/****** object: StoredProcedure [dbo].

  
   
   

    
    
   Current 1/3 page
   
    
    123 Next read the full text