存储过程调用存储过程实现分页功能

--分页存储过程，支持单表和多表

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_page]
@CurrentPage int, 
@PageSize int,
@Tcount int,
@Field_Info varchar(500),
@Table_info varchar(500),
@OtherTable varchar(1000),
@Field_id varchar(1000),
@intOrder int,
@otherwhere varchar(1000),
@RecordCount int output,
@PageCount int output
--@CurrentPage为显示那一页,@PageSize为每一页显示几行,
--@Field_info为要显示的字段可以为*,@Table_info为要查询的表或视图,
--@field_id为按这个字段排序,@intorder0为升序排1为降序排,
--@otherwhere为条件,@RecordCount为总行数,@PageCount为总页数
AS
begin
	DECLARE @MinPage int, @MaxPage int
	declare @sql varchar(1000)
	declare @sqlt nvarchar(300)
	declare @order varchar(4)
	declare @strWhere varchar(100)
	declare @sqlp varchar(1000)
	set @Field_Info = replace(@Field_Info,'''','')       --除去@field_info中的'
	set @Table_info = replace(@table_info,'''','')       --除去@table_info中的'
	set @Field_id = replace(@Field_id,'''','')            --除去@field_id中的'
	set @otherwhere = @otherwhere    --将@otherwhere中的'换成''让SQL语句正确释别'
	set @strWhere=' Where (1=1) '
	if @Tcount=1
		set @sqlt = 'SELECT @RecordCount = COUNT(*) FROM ' + @Table_Info
	else
		set @sqlt = 'SELECT @RecordCount = COUNT(*) FROM ' + @Table_Info + @OtherTable
	if @otherwhere like ''
		set @sqlt = @sqlt
	else
		set @sqlt = @sqlt+@strWhere+@otherwhere
	--print @sqlt
	exec sp_executesql @sqlt,N'@RecordCount int output',@RecordCount output     --如何将exec执行结果放入变量中,如果是字符串就要用N,N后面的变量一定要和@sqlt里面的变量同名
	IF @PageSize <= 0
		begin
			set @PageSize = 10
		end
	else if @PageSize > @RecordCount
		begin
			set @pageSize = @RecordCount
		end
	if @RecordCount=0
		set @pagecount=0
	else
	begin
		set @pagecount = @RecordCount / @PageSize
		if ((@RecordCount % @pagesize) != 0)					--如果除不尽则加一页
			begin
				set @PageCount = @RecordCount / @PageSize
				set @PageCount = @pagecount + 1
			end
		else
			begin
				set @pagecount = @RecordCount /@PageSize
			end
	end
	IF @CurrentPage <= 0
		begin
			set @CurrentPage = 1
		end
	else if @CurrentPage > @pagecount
		begin		
			set @currentpage = @pagecount		--如果输入页数大于总页数则符最后一页
		end
	SET @MinPage = (@CurrentPage - 1) * @PageSize + 1
	SET @MaxPage = @MinPage + @PageSize - 1
	set @sqlp=' ) as a where rownumber between ' + convert(varchar(10),@minpage) + ' and '+convert(varchar(10),@maxpage)
	BEGIN
		if @intorder = 0
			set @order = 'asc'
		else
			set @order = 'desc'
		if @Field_Info like ''
			set @field_Info = '*'
		if @Tcount=1
			set @sql = 'select * from (select ' +  @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info
		else
			set @sql = 'select * from (select ' +  @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info + @OtherTable
		if @otherwhere like ''
			set @sql = 'select * from (select ' +  @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info
		else
			set @sql = 'select * from (select ' +  @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info +  @OtherTable+ @strWhere+ @otherwhere
		
		set @sql=@sql+@sqlp
		--print @sql
		exec(@sql)
	END
end

--调用
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		ltj
-- Create date: 2008-5-29
-- Description:	查找新闻
-- =============================================
ALTER PROCEDURE [dbo].[SelectNewsByPage]
	@NewsId int=0,
	@NewsClassId int=0,
	@FromId varchar(20)=null,
	@ToDept varchar(50)=null,
	@ToPriv varchar(50)=null,
	@ToUser varchar(50)=null,
	@Subject varchar(200)=null,
	@Content varchar(100)=null,
	@CurrentPage int=1, 
	@PageSize int=10,
	@RecordCount int output,
	@PageCount int output
AS
BEGIN
	declare @Rcount int
	declare @Pcount int
	declare @strFilter varchar(1000)
	set @strFilter =''
	if @NewsId<>0
		Set @strFilter=@strFilter+' and NewsId='+Convert(varchar,@NewsId)
	if @NewsClassId<>0
		set @strFilter=@strFilter+' and NewsClassId='+Convert(varchar,@NewsClassId)
	if @FromId is not null
		set @strFilter=@strFilter+' and FromId='''+@FromId+''''
	if @ToDept is not null
		set @strFilter=@strFilter+' and ToDept Like ''%' + @ToDept + '%'''
	if @ToPriv is not null
		set @strFilter=@strFilter+' and ToPriv Like ''%' + @ToPriv + '%'''
	if @ToUser is not null
		set @strFilter=@strFilter+' and ToUser Like ''%' + @ToUser + '%'''
	if @Subject is not null
		set @strFilter=@strFilter+' and Subject Like ''%' + @Subject + '%'''
	if @Content is not null
		set @strFilter=@strFilter+' and Content Like ''%' + @Content + '%'''
	exec sp_page @CurrentPage,@PageSize,1,'','News','','NewsId',0,@strFilter,@Rcount output,@Pcount output
	set @RecordCount=@Rcount
	set @PageCount=@Pcount
END