USE RYPlatformManagerDB

GO

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

CREATE Proc [dbo].[WEB_PageView]

    @TableName        NVARCHAR(),            -- 表名

    @ReturnFields    NVARCHAR() = '*',    -- 查询列数

    @PageSize        INT = ,                -- 每页数目

    @PageIndex        INT = ,                -- 当前页码

    @Where            NVARCHAR() = '',    -- 查询条件

    @OrderBy        NVARCHAR(),            -- 排序字段

    @PageCount        INT OUTPUT,                -- 页码总数

    @RecordCount    INT OUTPUT                -- 记录总数

WITH ENCRYPTION AS

--设置属性

SET NOCOUNT ON

-- 变量定义

DECLARE @TotalRecord INT

DECLARE @TotalPage INT

DECLARE @CurrentPageSize INT

DECLARE @TotalRecordForPageIndex INT

BEGIN

    IF @Where IS NULL SET @Where=N''

    -- 记录总数

    DECLARE @countSql NVARCHAR()  

    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        

    SET @RecordCount=@TotalRecord

    SET @TotalPage=(@TotalRecord-)/@PageSize+

    SET @CurrentPageSize=(@PageIndex-)*@PageSize

    -- 返回总页数和总记录数

    SET @PageCount=@TotalPage

    SET @RecordCount=@TotalRecord

    -- 返回记录

    SET @TotalRecordForPageIndex=@PageIndex*@PageSize

    EXEC    ('SELECT *

            FROM (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+', ROW_NUMBER() OVER ('+@OrderBy+') AS PageView_RowNo

            FROM '+@TableName+ ' ' + @Where +' ) AS TempPageViewTable

            WHERE TempPageViewTable.PageView_RowNo >

            '+@CurrentPageSize)

END

RETURN 

GO