ALTER PROCEDURE [dbo].[Components_MultiPage]
    @TableName NVARCHAR(MAX),
    @Fields NVARCHAR(MAX),
    @Where NVARCHAR(MAX),
    @OrderBy NVARCHAR(MAX),
    @Groupby NVARCHAR(MAX),
    @PageIndex INT,
    @PageSize INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @BeginIndex INT
    DECLARE @EndIndex INT
    DECLARE @Sql NVARCHAR(MAX)
    DECLARE @SqlCount NVARCHAR(MAX)    
    DECLARE @GroupbyString NVARCHAR(MAX)
    IF(@Where = '') BEGIN
        SET @Where = '1 = 1'
    END
    SELECT @BeginIndex = (@PageIndex - 1) * @PageSize
    SELECT @EndIndex = @PageIndex * @PageSize
    
    IF(@Groupby <> N'') BEGIN
        SET @GroupbyString = N' GROUP BY ' + @Groupby
    END ELSE BEGIN
        SET @GroupbyString = N' '
    END
    SET @Sql = N'
    BEGIN WITH TheTable AS(
        SELECT ' + @Fields + ', ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS RowNumber
        FROM ' + @TableName + ' WHERE ' + @Where + ' ' + @GroupbyString + ')
        SELECT * FROM TheTable WHERE RowNumber > ' + CONVERT(NVARCHAR(255), @BeginIndex) + ' AND RowNumber <= ' + CONVERT(NVARCHAR(255), @EndIndex) + '
    END'
    SET @SqlCount = N'SELECT COUNT(*) AS [Rows] FROM ' + @TableName + ' WHERE ' + @Where  + '  '
    
--    PRINT @Sql
--    PRINT @SqlCount
    EXECUTE SP_EXECUTESQL @Sql
    EXECUTE SP_EXECUTESQL @SqlCount
--    DEBUG
--    EXECUTE SP_EXECUTESQL  dbo.Components_MultiPage 'User_Users', 'UserId', '', 'UserId', 1, 30
END

------解决方案--------------------
  这是SQL的存储过程,不是原创的.  
  
 CREATE   Procedure   BENNY_QueryRecordByPage  
 (  
 @PageSize   int,   --一页多少记录  
 @PageNumber   int,   --第几页  
 @QuerySql   varchar(1000),--???????,?*   From   Test   order   by   id   desc  
 @KeyField   varchar(500)    
 )  
 AS  
 Begin  
  
 Declare   @SqlTable   AS   varchar(1000)  
 Declare   @SqlText   AS   Varchar(1000)  
 Declare   @RCount   AS   int  
  
 Set   @SqlTable='Select   Top   '+CAST(@PageNumber*@PageSize   AS   varchar(30))+'   '+@QuerySql