分页存储过程(只用于SQL2005)

分页存储过程(只用于SQL2005)

/* 
 FUNCTION : 通用存储过程分页 
 
 AUTHOR  : Yong, Ji   
 ALTER  DATE : 2007-01-08  
  
 MODIFY DATE :  
  
 DESCRIPTION :  
 
 REMARK  :  
     
 CASE  : DBO.PROC_GO_PAGE 'SELECT TOP 3000 * FROM WTY_MST','WTY_MST_ID','ACCEPT_DT','',10,10 
 
*/
CREATE PROC DBO.PROC_GO_PAGE     
@TABLE_NAME VARCHAR(8000), --数据表名称     
@PK_NAME VARCHAR(50),  --主键名称     
@SORT_COLUMN_NAME VARCHAR(50), --排序的列名称     
@SORT_TYPE VARCHAR(10),  --排序方式:ASC,DESC     
@CURRENT_PAGE INT,  --当前页码     
@PAGE_SIZE INT   --页容量     
AS     
   
set nocount on   
BEGIN   
DECLARE @LONGSQL  VARCHAR(8000)   
      , @SHORTSQL NVARCHAR(4000)   
      , @TAB VARCHAR(40)   
      , @RECORD_COUNT int   
   
SELECT @TAB = 'PRT_' + REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),13),':','') ,' ','')    
              + '_' + convert(varchar,@@spid)   
   
SELECT @LONGSQL =   
    'select identity(int,1,1) t1rnum, t.* ' +   
       ' INTO TEMPDB.DBO.'+ @TAB +   
    '  from ( ' + @TABLE_NAME + ' ) t' +   
       '  ORDER BY T.'+@SORT_COLUMN_NAME+' ' +    
       case when @SORT_TYPE <> 'DESC' then 'ASC'   
            else @SORT_TYPE end   
   
EXEC(@LONGSQL)     
   
SELECT @RECORD_COUNT =@@ROWCOUNT    
   
select @SHORTSQL = 'select ' +   
       max(case when column_id = 2  then '  ' + c.name else '' end) +   
       max(case when column_id = 3  then ', ' + c.name else '' end) +   
       max(case when column_id = 4  then ', ' + c.name else '' end) +   
       max(case when column_id = 5  then ', ' + c.name else '' end) +   
       max(case when column_id = 6  then ', ' + c.name else '' end) +   
       max(case when column_id = 7  then ', ' + c.name else '' end) +   
       max(case when column_id = 8  then ', ' + c.name else '' end) +   
       max(case when column_id = 9  then ', ' + c.name else '' end) +   
       max(case when column_id = 10 then ', ' + c.name else '' end) +   
       max(case when column_id = 11 then ', ' + c.name else '' end) +   
       max(case when column_id = 12 then ', ' + c.name else '' end) +   
       max(case when column_id = 13 then ', ' + c.name else '' end) +   
       max(case when column_id = 14 then ', ' + c.name else '' end) +   
       max(case when column_id = 15 then ', ' + c.name else '' end) +   
       max(case when column_id = 16 then ', ' + c.name else '' end) +   
       max(case when column_id = 17 then ', ' + c.name else '' end) +   
       max(case when column_id = 18 then ', ' + c.name else '' end) +   
       max(case when column_id = 19 then ', ' + c.name else '' end) +   
       max(case when column_id = 20 then ', ' + c.name else '' end) +   
       max(case when column_id = 21 then ', ' + c.name else '' end) +   
       max(case when column_id = 22 then ', ' + c.name else '' end) +   
       max(case when column_id = 23 then ', ' + c.name else '' end) +   
       max(case when column_id = 24 then ', ' + c.name else '' end) +   
       max(case when column_id = 25 then ', ' + c.name else '' end) +   
       max(case when column_id = 26 then ', ' + c.name else '' end) + 
       max(case when column_id = 27 then ', ' + c.name else '' end) + 
       max(case when column_id = 28 then ', ' + c.name else '' end) + 
       max(case when column_id = 29 then ', ' + c.name else '' end) + 
       max(case when column_id = 30 then ', ' + c.name else '' end) + 
       '  from TEMPDB.DBO.'+ @TAB +   
    ' where t1rnum >  ' +    
       convert(varchar,   
       (CASE WHEN @CURRENT_PAGE <= 0 THEN 1   
             WHEN @CURRENT_PAGE >=
                  CEILING(CONVERT(decimal(15,2),@RECORD_COUNT)/@PAGE_SIZE)
             THEN CEILING(CONVERT(decimal(15,2),@RECORD_COUNT)/@PAGE_SIZE)
             ELSE @CURRENT_PAGE   
        END-1) * @PAGE_SIZE) +   
    '   and t1rnum <= ' +    
       convert(varchar,   
       CASE WHEN @CURRENT_PAGE <= 0 THEN 1   
            WHEN @CURRENT_PAGE >=
                 CEILING(CONVERT(decimal(15,2),@RECORD_COUNT)/@PAGE_SIZE)
            THEN CEILING(CONVERT(decimal(15,2),@RECORD_COUNT)/@PAGE_SIZE)
            ELSE @CURRENT_PAGE   
        END * @PAGE_SIZE)   
  from TEMPDB.sys.objects o   
  , TEMPDB.sys.columns c   
 where o.type = 'U'   
   and o.object_id = c.object_id   
   and o.name = @TAB   

--RETURN RESULT     
SELECT @RECORD_COUNT AS 'RECORD_COUNT',     
       CEILING(CONVERT(decimal(15,2),@RECORD_COUNT)/@PAGE_SIZE) AS 'PAGE_COUNT',     
       CASE WHEN @CURRENT_PAGE <= 0 THEN 1   
            WHEN @CURRENT_PAGE >=
                 CEILING(CONVERT(decimal(15,2),@RECORD_COUNT)/@PAGE_SIZE)
            THEN CEILING(CONVERT(decimal(15,2),@RECORD_COUNT)/@PAGE_SIZE)
            ELSE @CURRENT_PAGE   
        END AS 'CURRENT_PAGE',     
       @PAGE_SIZE    AS 'PAGE_SIZE',     
       @CURRENT_PAGE AS 'PAGE_IN'     
   
EXEC dbo.sp_executesql @SHORTSQL   
   
--SELECT @SHORTSQL = 'drop table TEMPDB.DBO.' + @TAB   
--EXEC dbo.sp_executesql @SHORTSQL   

END

 


GO

posted @ 2007-03-09 16:56  Nina  阅读(789)  评论(0编辑  收藏  举报