两种分页存储过程

第一种，通用性很强，效率也很高
Alter PROCEDURE [dbo].[Procedure_GetPaged]
(

    @WhereClause varchar (2000)  ,

    @OrderBy varchar (2000)  ,

    @PageIndex int   ,

    @PageSize int   
)
AS


                
                BEGIN
                DECLARE @PageLowerBound int
                DECLARE @PageUpperBound int
                DECLARE @RowsToReturn int
                
                -- First set the rowcount
                SET @RowsToReturn = @PageSize * (@PageIndex + 1)
                --SET ROWCOUNT @RowsToReturn
                
                -- Set the page bounds
                SET @PageLowerBound = @PageSize * @PageIndex
                SET @PageUpperBound = @PageLowerBound + @PageSize
                
                -- Create a temp table to store the select results
                CREATE TABLE #PageIndex
                (
                    [IndexId] int IDENTITY (1, 1) NOT NULL,
                    [DF_ID] int
                )
                
                -- Insert into the temp table
                declare @SQL as nvarchar(3500)
                SET @SQL = 'INSERT INTO #PageIndex (DF_ID)'
                SET @SQL = @SQL + ' SELECT [DF_ID]'
                SET @SQL = @SQL + ' FROM dbo.[Home_DataFileView]'
                IF LEN(@WhereClause) > 0
                BEGIN
                    SET @SQL = @SQL + ' WHERE ' + @WhereClause
                END
                 
                IF LEN(@OrderBy) > 0
                BEGIN
                    SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
                END
                
                -- Populate the temp table
                exec sp_executesql @SQL
                
                -- Return total count
                SELECT @@ROWCOUNT
                
                --Set RowCount After Total Rows is determined
                SET ROWCOUNT @RowsToReturn
                
                -- Return paged results
                SELECT O.*
                FROM
                    dbo.[Home_DataFileView] O,
                    #PageIndex PageIndex
                WHERE
O.[DF_ID] = PageIndex.[DF_ID] AND                     PageIndex.IndexID > @PageLowerBound AND
                    PageIndex.IndexID <= @PageUpperBound
                ORDER BY
                    PageIndex.IndexID
                
                END
            
            第二种，通用性很强，效率也很高 

 

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Procedure_DataFileView]
(
 @TableName varchar(50),            --表?名?
 @ReFieldsStr varchar(200) = '*',   --字?段?名?(全?部?字?段?为?*)
 @OrderString varchar(200),         --排?序?字?段?(必?须?!支?持?多?字?段?不?用?加?order by)
 @WhereString varchar(500) =N'',  --条?件?语?句?(不?用?加?where)
 @PageSize int,                     --每?页?多?少?条?记?录?
 @PageIndex int = 1 ,               --指?定?当?前?为?第?几?页?
 @TotalRecord int output            --返?回?总?记?录?数?
)
AS
BEGIN    

    --处?理?开?始?点?和?结?束?点?
    Declare @StartRecord int;
    Declare @EndRecord int; 
    Declare @TotalCountSql nvarchar(500); 
    Declare @SqlString nvarchar(2000);    
    set @StartRecord = (@PageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @PageSize - 1 
    SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总?记?录?数?语?句?
    SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查?询?语?句?
    --
    IF (@WhereString! = '' or @WhereString!=null)
        BEGIN
            SET @TotalCountSql=@TotalCountSql + '  where '+ @WhereString;
            SET @SqlString =@SqlString+ '  where '+ @WhereString;            
        END
    --第?一?次?执?行?得?到?
    --IF(@TotalRecord is null)
    --   BEGIN
           EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返?回?总?记?录?数?
    --  END
    ----执?行?主?语?句?
    set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' +  ltrim(str(@EndRecord));
    --Exec(@SqlString)
    Exec(@SqlString+';'+@TotalCountSql)    
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO