SQL2005 分页存储过程

最新推荐文章于 2024-11-06 10:53:30 发布

xiaogang3438792

最新推荐文章于 2024-11-06 10:53:30 发布

阅读量399

点赞数

文章标签：存储 sql

-- SQL2005 分页存储过程

CREATE PROCEDURE [ dbo ] . [ GetRecordFromPage2005 ]
    @SelectList             VARCHAR ( 2000 ),    -- 欲选择字段列表
    @TableSource         VARCHAR ( 100 ),    -- 表名或视图表
    @SearchCondition     VARCHAR ( 2000 ),    -- 查询条件
    @OrderExpression     VARCHAR ( 1000 ),    -- 排序表达式
    @PageIndex             INT = 1 ,        -- 页号,从0开始
    @PageSize             INT = 10         -- 页尺寸
AS
BEGIN
    IF @SelectList IS NULL OR LTRIM ( RTRIM ( @SelectList )) = ''
    BEGIN
        SET @SelectList = ' * '
    END
    PRINT @SelectList

    SET @SearchCondition = ISNULL ( @SearchCondition , '' )
    SET @SearchCondition = LTRIM ( RTRIM ( @SearchCondition ))
    IF @SearchCondition <> ''
    BEGIN
        IF UPPER ( SUBSTRING ( @SearchCondition , 1 , 5 )) <> ' WHERE '
        BEGIN
            SET @SearchCondition = ' WHERE ' + @SearchCondition
        END
    END
    PRINT @SearchCondition

    SET @OrderExpression = ISNULL ( @OrderExpression , '' )
    SET @OrderExpression = LTRIM ( RTRIM ( @OrderExpression ))
    IF @OrderExpression <> ''
    BEGIN
        IF UPPER ( SUBSTRING ( @OrderExpression , 1 , 5 )) <> ' WHERE '
        BEGIN
            SET @OrderExpression = ' ORDER BY ' + @OrderExpression
        END
    END
    PRINT @OrderExpression

    IF @PageIndex IS NULL OR @PageIndex < 1
    BEGIN
        SET @PageIndex = 1
    END
    PRINT @PageIndex
    IF @PageSize IS NULL OR @PageSize < 1
    BEGIN
        SET @PageSize = 10
    END
    PRINT @PageSize

    DECLARE @SqlQuery VARCHAR ( 4000 )

    SET @SqlQuery = ' SELECT ' + @SelectList + ' ,RowNumber
    FROM
        (SELECT ' + @SelectList + ' ,ROW_NUMBER() OVER( ' + @OrderExpression + ' ) AS RowNumber
          FROM ' + @TableSource + ' ' + @SearchCondition + ' ) AS RowNumberTableSource
    WHERE RowNumber BETWEEN ' + CAST ((( @PageIndex - 1 ) * @PageSize + 1 ) AS VARCHAR )
    + ' AND ' +
    CAST (( @PageIndex * @PageSize ) AS VARCHAR )
--     ORDER BY ' + @OrderExpression
    PRINT @SqlQuery
    SET NOCOUNT ON
    EXECUTE ( @SqlQuery )
    SET NOCOUNT OFF

    RETURN @@RowCount
END