SQL2005新分页功能使用

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[LogComm_select]
 @flag tinyint,
 @log_id int,
 @page_size int,
 @current_index int
AS
BEGIN
 SET NOCOUNT ON;

 --返回指定Log下符合条件的Comm信息
 IF @flag = '1'
  BEGIN
   WITH [LogCommTab] AS
   (
    SELECT ROW_NUMBER() OVER(ORDER BY [log_comm_date] DESC) AS [LogCommID], * FROM [LogComm] WHERE [log_id] = @log_id
   )
   SELECT * FROM [LogCommTab] WHERE [LogCommID] BETWEEN ((@current_index - 1) * @page_size + 1) AND @page_size * @current_index
  END

 --返回所有Comm数目
 ELSE IF @flag = '2'
  SELECT COUNT(*) FROM [LogComm]

 --返回最新n条Comm记录
 ELSE IF @flag = '3'
  SELECT TOP(@log_id) [Log].[log_menu_id], [LogComm].* FROM [LogComm] INNER JOIN [Log] ON [LogComm].[log_id] = [Log].[log_id] ORDER BY [LogComm].[log_comm_date] DESC

END

 

posted @ 2008-09-18 03:16  Jason.Bird  阅读(200)  评论(0编辑  收藏  举报