Sql2005分页存储过程

/*********************************************************************************
*      Function:  WEB_PageView                                                      
*      Description:                                                              
*             Sql2005分页存储过程                                                                                                         
*       Example:                                                                     
*              WEB_PageView 
*                @Tablename = 'T_shop', 
*                @Returnfields = '*',
*                @PageSize = 5, 
*                @PageIndex = 1, 
*                @Where = '',
*                @OrderBy=N'ORDER BY id desc',@PageCount =0,@RecordCount =0                                                   
*********************************************************************************/
use aiwaimaidb_new3
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[WEB_PageView]') and OBJECTPROPERTY(ID, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[WEB_PageView2]
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.WEB_PageView
    @TableName        NVARCHAR(200),            -- 表名
    @ReturnFields    NVARCHAR(1000) = '*',    -- 需要返回的列 
    @PageSize        INT = 10,                -- 每页记录数
    @PageIndex        INT = 1,                -- 当前页码
    @Where            NVARCHAR(1000) = '',    -- 查询条件
    @OrderBy        NVARCHAR(1000),            -- 排序字段名 最好为唯一主键    
    @PageCount        INT OUTPUT,                -- 页码总数
    @RecordCount    INT OUTPUT                -- 记录总数

--WITH ENCRYPTION AS
AS

--设置属性
SET NOCOUNT ON

-- 变量定义
DECLARE @TotalRecord INT
DECLARE @TotalPage INT
DECLARE @CurrentPageSize INT
DECLARE @StartIndex INT
DECLARE @EndIndex INT

BEGIN
    IF @Where IS NULL SET @Where=N''
    
    -- 记录总数的sql 语句
    DECLARE @countSql NVARCHAR(4000)  
    
    --计算总记录数
    IF @RecordCount IS NULL
        BEGIN
            SET @countSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where
            EXECUTE sp_executesql @countSql,N'@TotalRecord int out',@TotalRecord OUT
        END
    ELSE
        BEGIN
            SET @TotalRecord=@RecordCount
        END        
    
    --计算总页数
    IF @RecordCount%@PageSize=0
        BEGIN
            SET @TotalPage=@RecordCount/@PageSize
        END
    ELSE
        BEGIN
            SET @TotalPage=(@RecordCount/@PageSize)+1
        END
    
    -- 返回总页数和总记录数
    SET @PageCount=@TotalPage
    SET @RecordCount=@TotalRecord
        
    --计算起始索引和结束索引
    SET @CurrentPageSize=@PageIndex
    SET @StartIndex=(@CurrentPageSize - 1) * @PageSize + 1
    SET @EndIndex=@PageIndex * @PageSize
    
    EXEC    ('SELECT *
            FROM (SELECT ROW_NUMBER() OVER ('+@OrderBy+') AS PageView_RowNo,'+@ReturnFields+'  
            FROM '+@TableName+ ' ' + @Where +' ) AS TempPageViewTable 
            WHERE TempPageViewTable.PageView_RowNo between '+ @startIndex +' and '+ @endIndex)
    
END
RETURN 0
GO