• 支持DISTINCT的通用分页存储过程(SQL2005)


    /****** 对象:  StoredProcedure [dbo].[P_CommonPagination]    脚本日期: 07/22/2009 10:22:01 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:        
    -- Create date: 2008-12-16
    -- Description:    支持DISTINCT的通用分页存储过程(SQL2005)
    -- =============================================
    CREATE PROCEDURE [dbo].[P_CommonPagination]
    (
        -- 列名称
        @Fields            NVARCHAR(MAX) = '*',
        -- 表名称
        -- 不允许为空
        @Table            NVARCHAR(MAX),
        -- 查询条件
        @Where            NVARCHAR(MAX) = '',
        -- 排序的字段
        -- 不允许为空,无排序字段时可指定主键
        @Order            NVARCHAR(MAX),
        -- 页码
        @PageIndex        INT = 1,
        -- 每页数据
        @PageSize        INT = 10,
        -- 是否使用DISTINCT
        @UseDistinct    BIT = 0,
        -- 处理动作
        -- 0表示返回查询结果和总数,1表示只返回查询结果,2表示只返回总数
        @Action            TINYINT = 0,
        -- 记录总数
        @TotalCount        BIGINT OUTPUT
    )
    AS
    
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    
    -- **************************
    -- 未做输入参数检查
    -- **************************
    
    DECLARE @sql NVARCHAR(MAX)
    DECLARE @Distinct NVARCHAR(50)
    
    IF @Fields = ''
        SET @Fields = '*'
    SET @Order = ' ORDER BY ' + @Order
    IF @Where <> ''
        SET @Where = ' WHERE ' + @Where
    SET @TotalCount = 0
    IF @UseDistinct = 1
        SET @Distinct = ' DISTINCT '
    ELSE
        SET @Distinct = ''
    
    IF @Action <> 2
    BEGIN
        -- Get Paging Data
        IF @PageIndex = 1
            SET @sql = 'SELECT ' + @Distinct + ' TOP ' + STR(@PageSize) + ' ' + @Fields + ',0 AS RowNumber FROM ' + @Table + ' ' + @Where + ' ' + @Order
        ELSE
        BEGIN
            DECLARE @BeginRowNumber    INT
            SET @BeginRowNumber = (@PageIndex - 1) * @PageSize + 1
            DECLARE @EndRowNumber    INT
            SET @EndRowNumber = @PageIndex * @PageSize
    
            SET @sql = 'SELECT ' + @Fields + ' FROM (SELECT ' + @Fields + ',ROW_NUMBER() OVER(' + @Order + ') AS RowNumber FROM '
    
            IF @UseDistinct = 1
                SET @sql = @sql + '(SELECT DISTINCT ' + @Fields + ' FROM ' + @Table + ' ' + @Where + ') AS S'
            ELSE
                SET @sql = @sql + @Table + ' ' + @Where
    
            SET @sql = @sql + ') AS T WHERE RowNumber BETWEEN ' + STR(@BeginRowNumber)+' AND ' + STR(@EndRowNumber) + ' ORDER BY RowNumber'
        END
        --print @sql
        EXEC SP_EXECUTESQL @sql
    END
    
    IF @Action <>1
    BEGIN
        -- Get TotalCount
        SET @sql = 'SELECT @TotalCount=COUNT(1) FROM (SELECT ' + @Distinct + ' ' +@Fields+ ' FROM '+ @Table + ' ' + @Where +') AS temptable'
        --print @sql
        EXEC SP_EXECUTESQL @sql, N'@TotalCount INT OUTPUT', @TotalCount OUTPUT 
    END
    
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  • 相关阅读:
    mitm iptables ssltrip set ferret hamster
    SQL注入的常用函数和语句
    SQL注入的字符串连接函数
    SQL注入的分类
    DNS配置详解
    Linux的任务计划--cron入门
    Linux文件系统层次结构标准
    Linux的awk命令
    Linux的sed命令
    Linux的find命令
  • 原文地址:https://www.cnblogs.com/fm168/p/3384705.html
Copyright © 2020-2023  润新知