一支通用分页存储过程

  1
  2 /**/ /*************************************************************
  3**  Name        :    CurrentPage_Query
  4**  Creater        :    PPCoder2.0 Designed By PPTech Studio 
  5**  Create Date    :    2006-1-5 16:15:58
  6**  Modifer        :   
  7**  Modify Date    :    2006-1-5 16:15:58
  8**  Description :    store procedure for pager
  9**************************************************************/     
 10 ALTER   PROCEDURE  CurrentPage_Query
 11    @TableName           NVARCHAR ( 50 ),  -- TableName
 12    @OrderByColumn          NVARCHAR ( 50 ),  -- Sort by ths column
 13    @SortType      bit   =   1 ,          -- sort type:,0-asc,1-desc
 14    @QueryColumnList      NVARCHAR ( 800 ) = ' * ' , -- query column list
 15    @PageSize     int   =   20 ,          -- page size
 16    @CurrentPage         int   =   1 ,          -- current page
 17    @CustomCondition    NVARCHAR ( 800 ) = null , -- query condition
 18    @DoCount      bit   =   1 ,          -- whether count result count ,0 no ,1 yes
 19    @HasConstrainField   bit   =   1 ,
 20    @ReturnCount      int   =   0  OUTPUT    -- total pages
 21 AS
 22      DECLARE   @DynamicSQLString   NVARCHAR ( 4000 )
 23      DECLARE   @WhereFirstSegment   NVARCHAR ( 800 )
 24      DECLARE   @WhereDynamicSegment   NVARCHAR ( 800 )
 25      IF   @CustomCondition   is   null   or   rtrim ( @CustomCondition ) = ''
 26      BEGIN
 27          SET   @WhereFirstSegment = '  WHERE  '
 28          SET   @WhereDynamicSegment = '    '
 29      END
 30      ELSE
 31      BEGIN
 32          SET   @WhereFirstSegment = '  WHERE ( ' + @CustomCondition + ' ) AND  '
 33          SET   @WhereDynamicSegment = '  WHERE ( ' + @CustomCondition + ' )  '
 34      END
 35
 36      IF   @DoCount > 0  
 37      BEGIN
 38          SET   @DynamicSQLString = ' SELECT @ReturnCount=count(*) FROM  ' + @TableName + @WhereDynamicSegment
 39          EXEC  sp_executesql  @DynamicSQLString ,N ' @ReturnCount int OUTPUT ' , @ReturnCount  OUTPUT -- caculate the page count
 40      END
 41      print   @ReturnCount
 42 -- ------------------------------------------------------------------------------
 43      IF   @HasConstrainField   =   0
 44      BEGIN
 45          GOTO  noIdentity
 46      END
 47
 48 -- -------------------------------------------------------------------------------
 49      IF   @CurrentPage = 1
 50      BEGIN
 51          SET   @DynamicSQLString = ' SELECT TOP  ' + CAST ( @PageSize   AS   NVARCHAR ) + '   ' + @QueryColumnList + '  FROM  ' + @TableName
 52          SET   @DynamicSQLString   =   @DynamicSQLString   +   @WhereDynamicSegment
 53          SET   @DynamicSQLString   =   @DynamicSQLString   +   ' ORDER BY  ' + @OrderByColumn + CASE   @SortType   WHEN   0   THEN   ''   ELSE   '  DESC '   END
 54          EXEC ( @DynamicSQLString )
 55          RETURN
 56      END
 57
 58 -- --------------------------------------------------------------------------------
 59 hasIdentity:
 60
 61      IF   @SortType = 0
 62      BEGIN
 63          SET   @DynamicSQLString = ' SELECT TOP  ' + CAST ( @PageSize   AS   NVARCHAR ) + '   ' + @QueryColumnList + '  FROM  ' + @TableName
 64          SET   @DynamicSQLString   =   @DynamicSQLString   +   @WhereFirstSegment + @OrderByColumn + ' > '
 65          SET   @DynamicSQLString   =   @DynamicSQLString   +   ' (SELECT MAX( ' + @OrderByColumn + ' )   '
 66          SET   @DynamicSQLString   =   @DynamicSQLString   +   '  FROM (SELECT TOP  ' + CAST ( @PageSize * ( @CurrentPage - 1 )  AS   NVARCHAR ) + '   ' + @OrderByColumn + '  FROM  '
 67          SET   @DynamicSQLString   =   @DynamicSQLString   +   @TableName
 68          SET   @DynamicSQLString   =   @DynamicSQLString   +   @WhereDynamicSegment
 69          SET   @DynamicSQLString   =   @DynamicSQLString   +   ' ORDER BY  ' + @OrderByColumn + ' ) AS PagerTempTable)  '
 70          SET   @DynamicSQLString   =   @DynamicSQLString   +   ' ORDER BY  ' + @OrderByColumn
 71      END
 72      ELSE
 73      BEGIN
 74          SET   @DynamicSQLString = ' SELECT TOP  ' + CAST ( @PageSize   AS   NVARCHAR ) + '   ' + @QueryColumnList +   '  FROM  ' + @TableName
 75          SET   @DynamicSQLString   =   @DynamicSQLString   +   @WhereFirstSegment + @OrderByColumn + ' < '
 76          SET   @DynamicSQLString   =   @DynamicSQLString   +   ' (SELECT MIN( ' + @OrderByColumn + ' )   '
 77          SET   @DynamicSQLString   =   @DynamicSQLString   +   '  FROM (SELECT TOP  ' + CAST ( @PageSize * ( @CurrentPage - 1 )  AS   NVARCHAR ) + '   ' +   @OrderByColumn + '  FROM  '
 78          SET   @DynamicSQLString   =   @DynamicSQLString   +   @TableName
 79          SET   @DynamicSQLString   =   @DynamicSQLString   +   @WhereDynamicSegment
 80          SET   @DynamicSQLString   =   @DynamicSQLString   +   ' ORDER BY  ' + @OrderByColumn + '  DESC) AS PagerTempTable) '  
 81          SET   @DynamicSQLString   =   @DynamicSQLString   +   '  ORDER BY   '   +   @OrderByColumn + '  DESC '
 82      END
 83      EXEC ( @DynamicSQLString )
 84      RETURN
 85 -- ---------------------------------------------------------------------------------
 86
 87 -- ----------------------------------------------------------------------------------
 88 noIdentity:
 89      DECLARE   @OrderStr   NVARCHAR ( 1000 ) 
 90      DECLARE   @FdName   NVARCHAR ( 250 )
 91      DECLARE   @ID_MIN   NVARCHAR ( 20 )
 92      DECLARE   @ID_MAX   NVARCHAR ( 20 )
 93      DECLARE   @Obj_ID   int             
 94
 95      SELECT   @FdName = ' [ID_ ' + CAST ( NEWID ()  AS   NVARCHAR ( 40 )) + ' ] '
 96      SELECT   @ID_MIN = CAST ( @PageSize * ( @CurrentPage - 1 )  AS   NVARCHAR ( 20 ))
 97      SELECT   @ID_MAX = CAST ( @PageSize * @CurrentPage - 1   AS   NVARCHAR ( 20 ))
 98
 99      IF   @SortType > 0
100      BEGIN
101          SELECT   @OrderStr = '  ORDER BY  ' + @OrderByColumn + '  DESC '  
102      END
103      ELSE
104      BEGIN  
105          SELECT   @OrderStr = '  ORDER BY  ' + @OrderByColumn  
106      END
107            
108      SET   @DynamicSQLString = ' SELECT  ' + @FdName + ' =IDENTITY(int,0,1), ' + @QueryColumnList
109      SET   @DynamicSQLString   =   @DynamicSQLString   +   '  INTO #DynamicTable FROM  ' + @TableName + @WhereDynamicSegment + @OrderStr
110      SET   @DynamicSQLString   =   @DynamicSQLString   +   '  SELECT  ' + @QueryColumnList + '  FROM #DynamicTable where  ' + @FdName
111      SET   @DynamicSQLString   =   @DynamicSQLString   +   '  BETWEEN  ' + @ID_MIN + '  AND  ' + @ID_MAX
112      EXEC ( @DynamicSQLString )
113 RETURN  
114
115
116
117
118