--
查询满足条件的记录集,并获取指定页的数据
CREATE PROCEDURE Proc_QueryPages
@Fieldstr NVARCHAR ( 255 ) = ' * ' , -- 查询字段
@Tnamestr NVARCHAR ( 255 ) = '' , -- 查询表名
@Wherestr NVARCHAR ( 255 ) = '' , -- 查询条件
@Orderby Nvarchar ( 255 ) = ' order by id desc ' , -- 查询的排序
@PageSize int = 50 , -- 页尺寸
@PageIndex int = 1 , -- 页码
@mode int = 1 , -- 返回模式1为数据集,0为数据总数和总页数
@Pagemode int = 1 , -- 是否分页,1为分页,0表示不分页
@CountRs int = 1 output, -- --查询到的记录数
@Countpages int = 1 output -- --查询结果总页数
AS
declare @SQLstr Nvarchar ( 4000 )
declare @Wheretemp1 Nvarchar ( 1000 )
declare @Orderfield1 Nvarchar ( 1000 )
declare @OrderTmp1 Nvarchar ( 1000 )
declare @Orderby1 Nvarchar ( 1000 )
if @mode != 0 -- 判断结果是统计记录数还是返回数据集,非零返回数据集
BEGIN
if @Pagemode = 1 -- "1"为分页
begin
if @PageIndex = 1
if @Wherestr = ''
set @SQLstr = ' select top ' + ltrim ( str ( @PageIndex * @PageSize )) + ' ' + @Fieldstr + ' from ' + @Tnamestr + ' ' + @Orderby
else
set @SQLstr = ' select top ' + ltrim ( str ( @PageIndex * @PageSize )) + ' ' + @Fieldstr + ' from ' + @Tnamestr + ' where ' + @Wherestr + ' ' + @Orderby
-- 以上代码是查询第一页的,这样可以加速查询
else
IF charindex ( ' , ' , @Orderby ) > 0
Begin
set @SQLstr = ' SELECT TOP ' + ltrim ( str ( @PageSize )) + ' ' + @Fieldstr
+ ' FROM ' + @Tnamestr + ' WHERE (ID NOT IN (SELECT id FROM (SELECT top ' + ltrim ( str (( @PageIndex - 1 ) * @PageSize ))
+ ' * FROM ' + @Tnamestr + @Orderby + ' ) AS t)) ' + @Orderby
End
ELSE
Begin
set @Orderfield1 = replace ( replace ( replace ( @Orderby , ' order by ' , '' ), ' desc ' , '' ), ' asc ' , '' )
-- --设置排序的条件字符串
if charindex ( ' desc ' , @Orderby ) > 0
set @Wheretemp1 = ' where ' + @Orderfield1 + ' <(select min( ' + @Orderfield1 + ' ) from (select top '
else
set @Wheretemp1 = ' where ' + @Orderfield1 + ' >(select max( ' + @Orderfield1 + ' ) from (select top '
if @Wherestr = ''
set @SQLstr = ' select top ' + ltrim ( str ( @PageSize )) + ' ' + @Fieldstr
+ ' from ' + @Tnamestr + ' ' + @wheretemp1
+ ltrim ( str (( @PageIndex - 1 ) * @PageSize ))
+ ' ' + @Orderfield1 + ' from ' + @Tnamestr + ' ' + @Orderby + ' ) as T ) ' + @Orderby
ELSE
set @SQLstr = ' select top ' + ltrim ( str ( @PageSize )) + ' ' + @Fieldstr
+ ' from ' + @Tnamestr + ' ' + @wheretemp1
+ ltrim ( str (( @PageIndex - 1 ) * @PageSize ))
+ ' ' + @Orderfield1 + ' from ' + @Tnamestr + ' where ' + @Wherestr + ' ' + @Orderby + ' ) as T ) AND '
+ @Wherestr + ' ' + @Orderby
End
end
else -- 不分页模式代码开始
begin
if @Wherestr = ''
set @SQLstr = ' select ' + @Fieldstr + ' from ' + @Tnamestr + ' ' + @Orderby
else
set @SQLstr = ' select ' + @Fieldstr + ' from ' + @Tnamestr + ' where ' + @Wherestr + ' ' + @Orderby
end
exec ( @SQLstr ) -- 执行查询
-- print @SQLstr
END
else -- 返回记录数和总页数代码开始
BEGIN
if @Wherestr = ''
set @SQLstr = ' select @CountRs=count(*) from ' + @Tnamestr
Else
set @SQLstr = ' select @CountRs=count(*) from ' + @Tnamestr + ' where ' + @Wherestr
exec sp_executesql @SQLstr ,N ' @CountRs int out ' , @CountRs out
if @CountRs <= @PageSize
set @Countpages = 1
else
begin
set @Countpages = cast ( @CountRs / @PageSize as int )
if ( @CountPages * @PageSize != @CountRs )
set @Countpages = @CountRs / @PageSize + 1
end
-- print @CountRs
-- print @Countpages
END
GO
CREATE PROCEDURE Proc_QueryPages
@Fieldstr NVARCHAR ( 255 ) = ' * ' , -- 查询字段
@Tnamestr NVARCHAR ( 255 ) = '' , -- 查询表名
@Wherestr NVARCHAR ( 255 ) = '' , -- 查询条件
@Orderby Nvarchar ( 255 ) = ' order by id desc ' , -- 查询的排序
@PageSize int = 50 , -- 页尺寸
@PageIndex int = 1 , -- 页码
@mode int = 1 , -- 返回模式1为数据集,0为数据总数和总页数
@Pagemode int = 1 , -- 是否分页,1为分页,0表示不分页
@CountRs int = 1 output, -- --查询到的记录数
@Countpages int = 1 output -- --查询结果总页数
AS
declare @SQLstr Nvarchar ( 4000 )
declare @Wheretemp1 Nvarchar ( 1000 )
declare @Orderfield1 Nvarchar ( 1000 )
declare @OrderTmp1 Nvarchar ( 1000 )
declare @Orderby1 Nvarchar ( 1000 )
if @mode != 0 -- 判断结果是统计记录数还是返回数据集,非零返回数据集
BEGIN
if @Pagemode = 1 -- "1"为分页
begin
if @PageIndex = 1
if @Wherestr = ''
set @SQLstr = ' select top ' + ltrim ( str ( @PageIndex * @PageSize )) + ' ' + @Fieldstr + ' from ' + @Tnamestr + ' ' + @Orderby
else
set @SQLstr = ' select top ' + ltrim ( str ( @PageIndex * @PageSize )) + ' ' + @Fieldstr + ' from ' + @Tnamestr + ' where ' + @Wherestr + ' ' + @Orderby
-- 以上代码是查询第一页的,这样可以加速查询
else
IF charindex ( ' , ' , @Orderby ) > 0
Begin
set @SQLstr = ' SELECT TOP ' + ltrim ( str ( @PageSize )) + ' ' + @Fieldstr
+ ' FROM ' + @Tnamestr + ' WHERE (ID NOT IN (SELECT id FROM (SELECT top ' + ltrim ( str (( @PageIndex - 1 ) * @PageSize ))
+ ' * FROM ' + @Tnamestr + @Orderby + ' ) AS t)) ' + @Orderby
End
ELSE
Begin
set @Orderfield1 = replace ( replace ( replace ( @Orderby , ' order by ' , '' ), ' desc ' , '' ), ' asc ' , '' )
-- --设置排序的条件字符串
if charindex ( ' desc ' , @Orderby ) > 0
set @Wheretemp1 = ' where ' + @Orderfield1 + ' <(select min( ' + @Orderfield1 + ' ) from (select top '
else
set @Wheretemp1 = ' where ' + @Orderfield1 + ' >(select max( ' + @Orderfield1 + ' ) from (select top '
if @Wherestr = ''
set @SQLstr = ' select top ' + ltrim ( str ( @PageSize )) + ' ' + @Fieldstr
+ ' from ' + @Tnamestr + ' ' + @wheretemp1
+ ltrim ( str (( @PageIndex - 1 ) * @PageSize ))
+ ' ' + @Orderfield1 + ' from ' + @Tnamestr + ' ' + @Orderby + ' ) as T ) ' + @Orderby
ELSE
set @SQLstr = ' select top ' + ltrim ( str ( @PageSize )) + ' ' + @Fieldstr
+ ' from ' + @Tnamestr + ' ' + @wheretemp1
+ ltrim ( str (( @PageIndex - 1 ) * @PageSize ))
+ ' ' + @Orderfield1 + ' from ' + @Tnamestr + ' where ' + @Wherestr + ' ' + @Orderby + ' ) as T ) AND '
+ @Wherestr + ' ' + @Orderby
End
end
else -- 不分页模式代码开始
begin
if @Wherestr = ''
set @SQLstr = ' select ' + @Fieldstr + ' from ' + @Tnamestr + ' ' + @Orderby
else
set @SQLstr = ' select ' + @Fieldstr + ' from ' + @Tnamestr + ' where ' + @Wherestr + ' ' + @Orderby
end
exec ( @SQLstr ) -- 执行查询
-- print @SQLstr
END
else -- 返回记录数和总页数代码开始
BEGIN
if @Wherestr = ''
set @SQLstr = ' select @CountRs=count(*) from ' + @Tnamestr
Else
set @SQLstr = ' select @CountRs=count(*) from ' + @Tnamestr + ' where ' + @Wherestr
exec sp_executesql @SQLstr ,N ' @CountRs int out ' , @CountRs out
if @CountRs <= @PageSize
set @Countpages = 1
else
begin
set @Countpages = cast ( @CountRs / @PageSize as int )
if ( @CountPages * @PageSize != @CountRs )
set @Countpages = @CountRs / @PageSize + 1
end
-- print @CountRs
-- print @Countpages
END
GO