代码:

---

USE [TestDB]
GO
/****** 对象:  StoredProcedure [dbo].[sp_NetFog_Page]    脚本日期: 09/05/2006 08:20:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<NetFog,邱志>
-- Create date:		<2008,9,4>
-- Description:		<RowNumber分页存储过程>
-- example:		sp_NetFog_Page 0,90000,20,'ID','ID,AritcleTitle,Ftitle,otime,','TblTest','where id>0','order by id desc'
-- =============================================
CREATE PROCEDURE [dbo].[sp_NetFog_Page] 
(
	@nRecordCount BigInt OutPut,
	@nCurPage BigInt,
	@nPageSize BigInt,
	@nKey nvarchar(50),
	@nField nvarchar(1000),
	@nTable nvarchar(100),
	@nCondition nvarchar(1000),              --类似where id>50,前后不要有空格,可以为空
	@nOrder nvarchar(100)                    --类似Order by id desc,前后不要有空格
)
AS
BEGIN
	SET NOCOUNT ON;
	Declare @nSQL nvarchar(4000)
	Declare @tmp Int
	Declare @RowOrder nvarchar(50)
	Declare @TotalPage BigInt
	Declare @RowLowerBound BigInt
	Declare @RowUpperBound BigInt
	If @nRecordCount < 1
		Begin
			Set @nSQL='Select @nRecordCount=Count('+@nKey+') From '+@nTable+' '+@nCondition
			EXEC sp_executesql @nSQL,N'@nRecordCount int OUT',@nRecordCount OUT
		End
	Select @tmp = (@nRecordCount/@nPageSize)-Floor(@nRecordCount/@nPageSize)
	
	If @tmp <> 0
		Select @TotalPage = Floor(@nRecordCount/@nPageSize)+1
	Else
		Begin
			Select @TotalPage = Floor(@nRecordCount/@nPageSize)
		End
	If @nCurPage > @TotalPage
		Begin
			Select @nCurPage = @TotalPage
		End

	If @nRecordCount <= 2*@nPageSize
		Select @RowOrder = 'asc'
	Else
		Begin
			If @nCurPage*@nPageSize<=floor(@nRecordCount/2)
				Select @RowOrder = 'asc'
			Else
				Begin
					Select @RowOrder = 'desc'
				End
		End

	If @RowOrder = 'asc'
		If @nCurPage = 1
			Begin
			Select @RowLowerBound = 1
			If @nPageSize > @nRecordCount
				Select @RowUpperBound = @nRecordCount
			Else
				Begin
					Select @RowUpperBound = @nPageSize
				End
			End
		Else
			Begin
				Select @RowLowerBound = (@nCurPage-1)*@nPageSize+1
				If @nCurPage < @TotalPage
					Select @RowUpperBound = @nCurPage*@nPageSize
				Else
					Begin
						Select @RowUpperBound = @nRecordCount
					End
			End
	Else
		Begin
			If @nCurPage = @TotalPage
				Begin
				Select @RowLowerBound = 1
				If @tmp = 0
					Select @RowUpperBound = @nPageSize
				Else
					Begin
						Select @RowUpperBound = @nRecordCount-((@nCurPage-1)*@nPageSize)
					End
				End
			Else
				Begin
					Select @RowLowerBound = @nRecordCount-@nCurPage*@nPageSize+1
					Select @RowUpperBound = @nRecordCount-(@nCurPage-1)*@nPageSize
				End
		End
		Set @nSQL = 'select * from (select '+@nField+' ROW_NUMBER() OVER(order by '+@nKey+' '+@RowOrder+') as row from '+@nTable+' '+@nCondition+') a where row between '+Cast(@RowLowerBound AS nvarchar)+' and '+Cast(@RowUpperBound AS nvarchar)+' '+@nOrder
		--Print(@RowLowerBound)
		--Print(@RowUpperBound)
		--Print(@RowOrder)
		--Print(@nRecordCount)
		--Print(@nSQL)
		EXEC(@nSQL)
END

---