--sql 2000 分页存储过程

 
CREATE  PROCEDURE [dbo].[ProcCustomPage]
        (
            
@Table_Name               varchar(5000),              --表名
            @Sign_Record              varchar(50),               --主键
            @Filter_Condition         varchar(1000),             --筛选条件,不带where
            @Page_Size                int,                       --页大小
            @Page_Index               int,                      --页索引                 
            @TaxisField               varchar(1000),            --排序字段
            @Taxis_Sign               int,                       --排序方式 1为 DESC, 0为 ASC
            @Find_RecordList          varchar(1000),            --查找的字段
            @Record_Count             int                        --总记录数
         )
         
AS
            
BEGIN 
            
DECLARE  @Start_Number          int
            
DECLARE  @End_Number            int
            
DECLARE  @TopN_Number           int
         
DECLARE  @sSQL                  varchar(8000)
                 
if(@Find_RecordList='')
                 
BEGIN
                      
SELECT @Find_RecordList='*'
                 
END
         
SELECT @Start_Number =(@Page_Index-1* @Page_Size
            
IF @Start_Number<=0
         
SElECT @Start_Number=0
            
SELECT @End_Number=@Start_Number+@Page_Size
            
IF @End_Number>@Record_Count
         
SELECT @End_Number=@Record_Count
         
SELECT @TopN_Number=@End_Number-@Start_Number
         
IF @TopN_Number<=0
         
SELECT @TopN_Number=0
            
print @TopN_Number
         
print @Start_Number
         
print @End_Number
         
print @Record_Count
                 
IF @TaxisField=''
                 
begin
                    
select  @TaxisField=@Sign_Record
                 
end
         
IF @Taxis_Sign=0
              
BEGIN
                 
IF @Filter_Condition=''
                 
BEGIN
                     
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
                         WHERE 
'+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
                         WHERE 
'+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
                     ORDER BY 
'+@TaxisField+') order by '+@TaxisField+' DESC)order by '+@TaxisField
                 
END
                
ELSE
                
BEGIN
                
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
             WHERE 
'+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
             WHERE 
'+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
             WHERE 
'+@Filter_Condition+' ORDER BY '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField
                 
END
            
END
        
ELSE
            
BEGIN
            
IF @Filter_Condition=''
                
BEGIN
                    
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
                 WHERE 
'+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
                 WHERE 
'+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
                 ORDER BY 
'+@TaxisField+' DESC) order by '+@TaxisField+')order by '+@TaxisField+' DESC'
             
END
            
ELSE
            
BEGIN
                
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
             WHERE 
'+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
             WHERE 
'+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
             WHERE 
'+@Filter_Condition+' ORDER BY '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC'
         
END
            
END
            
EXEC (@sSQL)
            
IF @@ERROR<>0
            
RETURN -3              
         
RETURN 0
         
END
         
         
PRINT  @sSQL
  
GO

--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+1AS VARCHAR
    
+ ' AND ' + 
    
CAST((@PageIndex * @PageSizeAS VARCHAR
--    ORDER BY ' + @OrderExpression
    PRINT @SqlQuery
    
SET NOCOUNT ON
    
EXECUTE(@SqlQuery)
    
SET NOCOUNT OFF
 
    
RETURN @@RowCount
END
posted on 2009-07-19 08:34  钱途无梁  阅读(778)  评论(1编辑  收藏  举报