SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO -- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: 分页存储过程
-- =============================================
ALTER PROCEDURE [dbo].[GetRecordFromPage]
@tbName varchar(255), --表名
@fIdName varchar(255), --字段名
@PageSize int =10, --页尺寸
@PageIndex int=1, --页码
@SumCount int output, --返回记录总数
@PageCount int output, --返回总页数
@OrderType bit=0, --设置排序类型
@strWhere varchar(1000)='' --查询条件(注意不要加where)
AS
BEGIN
declare @strSQL varchar(5000) --主SQL语句
declare @tmpSQL varchar(150) --临时SQL语句
declare @strOrder varchar (400) --排序类型
SET NOCOUNT ON;
--设置排序类型
if @OrderType!=0
begin
set @tmpSQL=' <(select min'
set @strOrder='order by ['+@fIdName+'] desc'
end
else
begin
set @tmpSQL='>(select max'
set @strOrder='order by ['+@fIdName+'] asc'
end --如果是查询第一页的就直接输出
if @PageIndex=1
begin
if @strWhere!=''
begin
set @strSQL='select top '+ str(@PageSize)+' * from ['+@tbName+'] where '+@strWhere+' '+ @strOrder
end
else
begin
set @strSQL='select top '+ str(@PageSize)+' * from ['+@tbName+']'+@strOrder
end
end
else --如果不是查询的不是第一页的数据
begin
if @strWhere!=''
begin
set @strSQL='select top '+str(@PageSize)+'* from ['+@tbName+'] where ['+ @fIdName+']'+@tmpSQL+'(['+@fIdName+']) from ( select Top'+
str((@PageIndex-1)*@PageSize)+'['+@fIdName+'] from ['+@tbName+'] where ' +@strWhere+' ' +@strOrder+') as tbTmp ) and '+@strWhere+' '+@strOrder
end
else
begin
set @strSQL='select top '+str(@PageSize)+'* from ['+@tbName+'] where ['+ @fIdName+']'+@tmpSQL+'(['+@fIdName+']) from ( select Top'+
str((@PageIndex-1)*@PageSize)+'['+@fIdName+'] from ['+@tbName+']'+@strOrder+') as tbTmp )'+@strOrder
end
end exec (@strSQL)
END
GO SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO 我想把 数据项总数,和页总数都输出出来。但是就是实现不了,能帮忙把 实现代码写处来吗?
GO
SET QUOTED_IDENTIFIER ON
GO -- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: 分页存储过程
-- =============================================
ALTER PROCEDURE [dbo].[GetRecordFromPage]
@tbName varchar(255), --表名
@fIdName varchar(255), --字段名
@PageSize int =10, --页尺寸
@PageIndex int=1, --页码
@SumCount int output, --返回记录总数
@PageCount int output, --返回总页数
@OrderType bit=0, --设置排序类型
@strWhere varchar(1000)='' --查询条件(注意不要加where)
AS
BEGIN
declare @strSQL varchar(5000) --主SQL语句
declare @tmpSQL varchar(150) --临时SQL语句
declare @strOrder varchar (400) --排序类型
SET NOCOUNT ON;
--设置排序类型
if @OrderType!=0
begin
set @tmpSQL=' <(select min'
set @strOrder='order by ['+@fIdName+'] desc'
end
else
begin
set @tmpSQL='>(select max'
set @strOrder='order by ['+@fIdName+'] asc'
end --如果是查询第一页的就直接输出
if @PageIndex=1
begin
if @strWhere!=''
begin
set @strSQL='select top '+ str(@PageSize)+' * from ['+@tbName+'] where '+@strWhere+' '+ @strOrder
end
else
begin
set @strSQL='select top '+ str(@PageSize)+' * from ['+@tbName+']'+@strOrder
end
end
else --如果不是查询的不是第一页的数据
begin
if @strWhere!=''
begin
set @strSQL='select top '+str(@PageSize)+'* from ['+@tbName+'] where ['+ @fIdName+']'+@tmpSQL+'(['+@fIdName+']) from ( select Top'+
str((@PageIndex-1)*@PageSize)+'['+@fIdName+'] from ['+@tbName+'] where ' +@strWhere+' ' +@strOrder+') as tbTmp ) and '+@strWhere+' '+@strOrder
end
else
begin
set @strSQL='select top '+str(@PageSize)+'* from ['+@tbName+'] where ['+ @fIdName+']'+@tmpSQL+'(['+@fIdName+']) from ( select Top'+
str((@PageIndex-1)*@PageSize)+'['+@fIdName+'] from ['+@tbName+']'+@strOrder+') as tbTmp )'+@strOrder
end
end exec (@strSQL)
END
GO SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO 我想把 数据项总数,和页总数都输出出来。但是就是实现不了,能帮忙把 实现代码写处来吗?
GO
SET QUOTED_IDENTIFIER ON
GO -- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: 分页存储过程
-- =============================================
ALTER PROCEDURE [dbo].[GetRecordFromPage]
@tbName varchar(255), --表名
@fIdName varchar(255), --字段名
@PageSize int =10, --页尺寸
@PageIndex int=1, --页码
@SumCount int output, --返回记录总数
@PageCount int output, --返回总页数
@OrderType bit=0, --设置排序类型
@strWhere varchar(1000)='' --查询条件(注意不要加where)
AS
BEGIN
declare @strSQL varchar(5000) --主SQL语句
declare @tmpSQL varchar(150) --临时SQL语句
declare @strOrder varchar (400) --排序类型
SET NOCOUNT ON;
--设置排序类型
if @OrderType!=0
begin
set @tmpSQL=' <(select min'
set @strOrder='order by ['+@fIdName+'] desc'
end
else
begin
set @tmpSQL='>(select max'
set @strOrder='order by ['+@fIdName+'] asc'
end --如果是查询第一页的就直接输出
if @PageIndex=1
begin
if @strWhere!=''
begin
set @strSQL='select top '+ str(@PageSize)+' * from ['+@tbName+'] where '+@strWhere+' '+ @strOrder
end
else
begin
set @strSQL='select top '+ str(@PageSize)+' * from ['+@tbName+']'+@strOrder
end
end
else --如果不是查询的不是第一页的数据
begin
if @strWhere!=''
begin
set @strSQL='select top '+str(@PageSize)+'* from ['+@tbName+'] where ['+ @fIdName+']'+@tmpSQL+'(['+@fIdName+']) from ( select Top'+
str((@PageIndex-1)*@PageSize)+'['+@fIdName+'] from ['+@tbName+'] where ' +@strWhere+' ' +@strOrder+') as tbTmp ) and '+@strWhere+' '+@strOrder
end
else
begin
set @strSQL='select top '+str(@PageSize)+'* from ['+@tbName+'] where ['+ @fIdName+']'+@tmpSQL+'(['+@fIdName+']) from ( select Top'+
str((@PageIndex-1)*@PageSize)+'['+@fIdName+'] from ['+@tbName+']'+@strOrder+') as tbTmp )'+@strOrder
end
end exec (@strSQL) declare @sqls nvarchar(4000)
set @sqls='select @a=count(*) from ['+@tbName+'] where '+@strWhere
exec sp_executesql @sqls,N'@a int output',@SumCount output
select @SumCount --总记录数
select @PageCount=case when @SumCount>0 then @SumCount/@PageSize+1 else 0 end --总页数
END
GO SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
declare @sqls nvarchar(4000)
set @sqls='select @a=count(*) from ['+@tbName+'] where '+@strWhere
exec sp_executesql @sqls,N'@a int output',@SumCount output
select @SumCount --总记录数
print @SumCount%@PageSize
select @PageCount=case when @SumCount%@PageSize>0 then @SumCount/@PageSize+1 else @SumCount/@PageSize end
--总页数