分页存储过程.sql

sql2000分页存储过程（两个文件）

/**分页存储过程**/
Create Procedure [Pr_Pagination]
	@sIdField varchar(50),			/**ID字段名**/
	@sSelectField varchar(200),		/**需要获取的字段名**/
	@sTableName varchar(50),		/**ID字段名**/
	@sCondition varchar(500),		/**条件**/
	@sOrderBy varchar(200),			/**顺序**/
	@nPageSize int,				/**页面大小，即显示多少条数据**/
	@nPageIndex int,			/**当前页码**/
	@nRsCount int,				/**总记录数**/
	@nPageCount int,			/**总页数**/
	/**以上两个参数是由程序先获取记录总数，判断是否为空，然后再调用本过程**/
	@bIsDesc bit				/**是否倒序**/
As
	Set NoCount On
	Declare @Sql varchar(1000)
	Declare @Condition1 varchar(500)
	Declare @Condition2 varchar(500)
	Declare @OrderBy1 varchar(200)
	Declare @OrderBy2 varchar(200)
	/**判断取出条件**/
	If @sCondition != '' Begin
		Set @Condition1 = ' Where ' + @sCondition + ' '
		Set @Condition2 = ' And ' + @sCondition + ' '
	End
	/**判断顺序条件**/
	If @sOrderBy != '' Begin
		Set @OrderBy1 = ' Order By ' + @sOrderBy + ','
		Set @OrderBy2 = @sOrderBy + ','
	End
	/**判断是否第一页**/
	If @nPageIndex = 1 Begin
		/**判断升或降序**/
		If @bIsDesc = 1 Begin
			Set @OrderBy1 = @OrderBy1 + @sIdField + ' Desc'
		End
		Else Begin
			Set @OrderBy1 = @OrderBy1 + @sIdField + ' Asc'
		End
		Set @Sql = 'Select Top ' + Convert(varchar, @nPageSize) + ' ' + @sSelectField +
			   ' From ' + @sTableName + @Condition1 + @OrderBy1
	End
	Else Begin
		Declare @OrderBy3 varchar(4)
		Declare @OrderBy4 varchar(4)
		Declare @OrderBy5 varchar(4)
		Declare @Compare varchar(1)
		Declare @PageN int
		/**判断是前半页码还是后半页码，使得因数据多时，越大的页码速度提高**/
		If ((@nPageIndex*2) > @nPageCount) Begin
			/**设置从后面读取多少条数据**/
			Set @PageN = @nRsCount - (@nPageSize*@nPageIndex)
			If @PageN<=0 Begin
				Set @PageN = @nRsCount - @nPageSize*(@nPageIndex-1)
			End
			/**如果是降序**/
			If @bIsDesc = 1 Begin
				Set @Compare = '<'
				Set @OrderBy3 = ' Desc'
				Set @OrderBy4 = ' Asc'
				Set @OrderBy5 = ' Asc'
			End
			/**如果是升序**/
			Else Begin
				Set @Compare = '>'
				Set @OrderBy3 = ' Asc'
				Set @OrderBy4 = ' Desc'
				Set @OrderBy5 = ' Desc'
			End
		End
		Else Begin
			/**设置从前面读取多少条数据**/
			Set @PageN = @nPageSize*(@nPageIndex-1)
			/**如果是降序**/
			If @bIsDesc = 1 Begin
				Set @Compare = '<'
				Set @OrderBy3 = ' Desc'
				Set @OrderBy4 = ' Asc'
				Set @OrderBy5 = ' Desc'
			End
			/**如果是升序**/
			Else Begin
				Set @Compare = '>'
				Set @OrderBy3 = ' Asc'
				Set @OrderBy4 = ' Desc'
				Set @OrderBy5 = ' Asc'
			End
		End
		/**设置sql执行语句**/
		Set @Sql = 'Select Top ' + Convert(varchar, @nPageSize) + ' ' + @sSelectField + ' From ' +
			   @sTableName + ' Where ' + @sIdField + @Compare +
			   '(' +
			   'Select Top 1 ' + @sIdField + ' From (' +
					'Select Top ' + @PageN + ' ' + @sIdField + ' From ' + 
					@sTableName + @Condition1 + 'Order By ' + @sIdField + @OrderBy5 +
				')' + 'Order By ' + @sIdField + @OrderBy4 +
			   ')' + @Condition2 + 'Order By ' + @OrderBy2 + @sIdField + @OrderBy3
	End
	/**开始执行**/
	Exec sp_executesql @Sql
	Set NoCount Off
Go