Magic.NET

/****** Object:  StoredProcedure [dbo].[GetRecordFromPage]    Script Date: 12/15/2011 17:46:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

    CREATE PROCEDURE [dbo].[GetRecordFromPage]   
        @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 

GO

 

---

人生到處知何似
應似飛鴻踏雪泥