Sql Server 2005分页存储过程

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_Pages')
	BEGIN
		PRINT N'删除存储过程 sp_Pages'
		DROP  PROCEDURE  sp_Pages
	END

GO

PRINT N'生成存储过程 sp_Pages'
GO
CREATE PROCEDURE sp_Pages
	@Table nvarchar(4000),			-- 表名
	@Where nvarchar(4000) = N'',	-- 条件
	@PageSize int,					-- 每页的大小(行数)
	@Page int,						-- 当前页序号
	@FdShow nvarchar (4000) = N'',	-- 要显示的字段列表，如果查询结果有标识字段，需要指定此值，且不包含标识字段
	@FdOrder nvarchar (1000) = N'',	-- 排序字段列表
	@RecordSum int output			-- 记录总数
	
AS
    IF @FdShow IS NULL OR LTRIM(RTRIM(@FdShow)) = ''
    BEGIN
        SET @FdShow = '*'
    END
    
    SET @Where = ISNULL(@Where,'')
    SET @Where = LTRIM(RTRIM(@Where))
    IF @Where <> ''
    BEGIN
        IF UPPER(SUBSTRING(@Where,1,5)) <> 'WHERE'
        BEGIN
            SET @Where = 'WHERE 1 = 1 ' + @Where
        END
    END

	IF @PageSize > 0
	BEGIN
		SET @FdOrder = NULLIF(LTRIM(RTRIM(@FdOrder)),'')
		SET @FdOrder = ISNULL(@FdOrder,'id')
	END

    SET @FdOrder = LTRIM(RTRIM(@FdOrder))
    IF @FdOrder <> ''
    BEGIN
        IF UPPER(SUBSTRING(@FdOrder,1,8)) <> 'ORDER BY'
        BEGIN
            SET @FdOrder = 'ORDER BY ' + @FdOrder
        END
    END

    DECLARE @SqlQuery NVARCHAR(MAX)
    DECLARE @SqlCount NVARCHAR(MAX)
    
    -- 获取记录总数
	set @SqlCount = 'select @RecordSum = count(*) from ' + @Table + ' ' + @Where
	exec sp_executesql @SqlCount, N'@RecordSum int output', @RecordSum output

	IF @RecordSum = 0
		SET @Page = 0
	ELSE IF (@RecordSum - 1) / @PageSize + 1 < @Page
		SET @Page = (@RecordSum - 1) / @PageSize + 1
	
	IF @PageSize = -1
	BEGIN
		SET @SqlQuery = '
			SELECT ' + @FdShow + '
				FROM '+@Table+' '+ @Where
	END
	ELSE
	BEGIN
		SET @SqlQuery = '
			SELECT ' + @FdShow + ',RowNumber
				FROM (
					SELECT ' + @FdShow + ',ROW_NUMBER() OVER( '+ @FdOrder +') AS RowNumber 
						FROM '+@Table+' '+ @Where +') AS RowNumberTableSource 
						WHERE RowNumber BETWEEN ' + CAST(((@Page - 1) * @PageSize+1) AS VARCHAR) + '
							AND ' + CAST((@Page * @PageSize) AS VARCHAR)
	END

    EXECUTE(@SqlQuery)
    
    RETURN 0
GO

GRANT EXEC ON sp_Pages TO PUBLIC

GO