USE [USInfo]
GO
/****** 对象:  StoredProcedure [dbo].[ent_Pages]    脚本日期: 10/26/2011 15:33:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

                
CREATE PROCEDURE [dbo].[ent_Pages]                    
    (    
    @Primaryid varchar(100),                
    @TableName varchar(1000),                
    @FieldList varchar(2000),                           
    @StrWhere varchar(2000),                   
    @OrderField varchar(1000),                 
    @PageIndex int,                
    @PageSize int,                
    @PageCount int output,                
    @RecordCount int output                
    )                     
AS                     
                  
SET NOCOUNT ON                   
                
DECLARE @intRootRecordCount int                  
DECLARE @SqlQuery nvarchar(4000)                   
                   
SET @PageCount=0              
SET @SqlQuery= 'SET NOCOUNT ON;SELECT @SPintRootRecordCount=Count(*) FROM '+@TableName+' WHERE '+@StrWhere                    
EXECUTE sp_executesql @SqlQuery,N'@SPintRootRecordCount int OUTPUT',@SPintRootRecordCount=@intRootRecordCount OUTPUT                  
SELECT @RecordCount = @intRootRecordCount                  
IF @RecordCount=0                  
    return                  
ELSE                  
  BEGIN                  
   SELECT @PageCount=(@RecordCount+@PageSize-1)/@PageSize                           
   IF @PageIndex=1    
          IF @StrWhere!=''    
        SELECT @SqlQuery='SELECT TOP '+CAST(@PageSize AS VARCHAR(20))+' '+@FieldList+' FROM ' +@TableName+' WHERE '+@StrWhere +' ORDER BY '+@OrderField        
          ELSE    
              SELECT @SqlQuery='SELECT TOP '+CAST(@PageSize AS VARCHAR(20))+' '+@FieldList+' FROM ' +@TableName+' ORDER BY '+@OrderField        
   ELSE         
    SET @SqlQuery='SELECT '+@FieldList+' FROM '+@TableName+' WHERE '+@StrWhere +' AND a.'+@Primaryid+' in(SELECT '+@Primaryid+' FROM (SELECT row_number() over(ORDER BY '+@OrderField+') as rownum,a.'+@Primaryid+' FROM '+@TableName+' WHERE '+@StrWhere +') as temptbl                    
    WHERE rownum BETWEEN ('+cast(@PageIndex as varchar)+'-1)*'+cast(@PageSize as varchar)+'+1 and '+cast(@PageIndex as varchar)+'*'+cast(@PageSize as varchar) + ') ORDER BY '+@OrderField             
   END     
      SET NOCOUNT ON              
      EXECUTE(@SqlQuery)                    
      SET NOCOUNT OFF

望ORACLE高手帮忙改写