SQL 分页通用存储过程

USE [DB]
GO
/****** Object:  StoredProcedure [dbo].[SP_AspNetPager]    Script Date: 10/23/2015 16:37:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[SP_AspNetPager]
    @PageSize INT = 10 , -- 页尺寸
    @PageIndex INT = 1 , -- 页码
    @TableName VARCHAR(255) , -- 表名 
    @KeyFields VARCHAR(255) = '' , -- 排序的字段名
    @ShowFields VARCHAR(1000) = '*' , -- 需要返回的列
    @OrderType BIT = 0 , -- 设置排序类型, 非 0 值则降序
    @WhereString VARCHAR(1500) = '', -- 查询条件 (注意: 不要加 where)
    @OrderFields VARCHAR(1000)='',      --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
    @RecordCount int OUTPUT             --总页数
AS 
    DECLARE @StrSQL VARCHAR(5000)       -- 主语句
    DECLARE @StrTmp VARCHAR(110)        -- 临时变量
    DECLARE @StrOrder VARCHAR(400)        -- 排序类型

    IF @RecordCount IS NULL
    BEGIN
        DECLARE @sql nvarchar(4000)
        SET @sql=N'SELECT @RecordCount=COUNT(*)'
            +N' FROM '+@TableName
            +N' WHERE '+@WhereString
        EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT
    end
    
    --以上代码的意思是如果@DoCount传递过来的不是0，就执行总数统计。以下的所有代码都是@DoCount为0的情况
    IF @OrderType != 0 
    BEGIN
        SET @StrTmp = '<(select min'
        SET @StrOrder = ' order by [' + @KeyFields + '] desc'
        --如果@OrderType不是0，就执行降序，这句很重要！
    END
    ELSE 
    BEGIN
        SET @StrTmp = '>(select max'
        SET @StrOrder = ' order by [' + @KeyFields + '] asc'
    END
    IF @PageIndex = 1 
    BEGIN
        IF @WhereString != '' 
            SET @StrSQL = 'select top ' + STR(@PageSize) + ' '
                + @ShowFields + '  from [' + @TableName
                + '] where ' + @WhereString + ' ' + @StrOrder
        ELSE 
            SET @StrSQL = 'select top ' + STR(@PageSize) + ' '
                + @ShowFields + '  from [' + @TableName + '] '
                + @StrOrder
        --如果是第一页就执行以上代码，这样会加快执行速度
    END
    ELSE 
    BEGIN
        --以下代码赋予了@StrSQL以真正执行的SQL代码
        SET @StrSQL = 'select top ' + STR(@PageSize) + ' '
            + @ShowFields + '  from [' + @TableName + '] where ['
            + @KeyFields + ']' + @StrTmp + '([' + @KeyFields
            + ']) from (select top ' + STR(( @PageIndex - 1 )
                                           * @PageSize) + ' ['
            + @KeyFields + '] from [' + @TableName + ']' + @StrOrder
            + ') as tblTmp)' + @StrOrder
        IF @WhereString != '' 
            SET @StrSQL = 'select top ' + STR(@PageSize) + ' '
                + @ShowFields + '  from [' + @TableName
                + '] where [' + @KeyFields + ']' + @StrTmp + '(['
                + @KeyFields + ']) from (select top '
                + STR(( @PageIndex - 1 ) * @PageSize) + ' ['
                + @KeyFields + '] from [' + @TableName + '] where '
                + @WhereString + ' ' + @StrOrder
                + ') as tblTmp) and ' + @WhereString + ' '
                + @StrOrder
    END 
    EXEC (@StrSQL)