/**/
/*
过程说明:简单分页程序(要么分页,要么统计)
编 写 人:
编写时间:
*/
CREATE PROCEDURE Page
@tblName varchar ( 255 ), -- 表名
@fldName varchar ( 255 ), -- 字段名
@PageSize int = 10 , -- 页尺寸
@PageIndex int = 1 , -- 页码
@IsCount bit = 0 , -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0 , -- 设置排序类型, 非 0 值则降序
@strWhere varchar ( 1000 ) = '' , -- 查询条件 (注意: 不要加 where)
@FieldsList varchar ( 255 ) -- 字段列表(要选出的字段)
AS
declare @strSQL varchar ( 1000 ), -- 主语句
@strTmp varchar ( 300 ), -- 临时变量
@strOrder varchar ( 400 ) -- 排序类型
set @strTmp = ''
set @strOrder = ''
-- 只统计总记录数
if @IsCount != 0
begin
if @strWhere != ''
set @strTmp = " where (" + @strWhere + ")"
set @strSQL = " select count ( * ) as Total from [ " + @tblName + " ] " + @strTmp
end
-- 不统计总记录,分页(查询表中所有记录)
else
begin
-- 如果是第一页
if @PageIndex = 1
begin
-- 排序
if @OrderType != 0
set @strOrder = " order by [ " + @fldName +" ] desc "
else
set @strOrder = " order by [ " + @fldName +" ] asc "
-- 条件
if @strWhere != ''
set @strTmp = " where (" + @strWhere + ")"
set @strSQL = " select top " + str ( @PageSize ) + " " + @FieldsList + " from [ "
+ @tblName + " ] " + @strTmp + " " + @strOrder
end
-- 如果不是第一页
else
begin
-- 排序
if @OrderType != 0
begin
set @strTmp = " < ( select min "
set @strOrder = " order by [ " + @fldName +" ] desc "
end
else
begin
set @strTmp = " > ( select max "
set @strOrder = " order by [ " + @fldName +" ] asc "
end
set @strSQL = " select top " + str ( @PageSize ) + " " + @FieldsList + " from [ "
+ @tblName + " ] where [ " + @fldName + " ] " + @strTmp + "( [ "
+ @fldName + " ] ) from ( select top " + str (( @PageIndex - 1 ) * @PageSize ) + " [ "
+ @fldName + " ] from [ " + @tblName + " ] " + @strOrder + ") as tblTmp)"
+ @strOrder
-- 条件
if @strWhere != ''
set @strSQL = " select top " + str ( @PageSize ) + " " + @FieldsList + " from [ "
+ @tblName + " ] where [ " + @fldName + " ] " + @strTmp + "( [ "
+ @fldName + " ] ) from ( select top " + str (( @PageIndex - 1 ) * @PageSize ) + " [ "
+ @fldName + " ] from [ " + @tblName + " ] where (" + @strWhere + ") "
+ @strOrder + ") as tblTmp) and (" + @strWhere + ") " + @strOrder
end
end
exec ( @strSQL )
过程说明:简单分页程序(要么分页,要么统计)
编 写 人:
编写时间:
*/
CREATE PROCEDURE Page
@tblName varchar ( 255 ), -- 表名
@fldName varchar ( 255 ), -- 字段名
@PageSize int = 10 , -- 页尺寸
@PageIndex int = 1 , -- 页码
@IsCount bit = 0 , -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0 , -- 设置排序类型, 非 0 值则降序
@strWhere varchar ( 1000 ) = '' , -- 查询条件 (注意: 不要加 where)
@FieldsList varchar ( 255 ) -- 字段列表(要选出的字段)
AS
declare @strSQL varchar ( 1000 ), -- 主语句
@strTmp varchar ( 300 ), -- 临时变量
@strOrder varchar ( 400 ) -- 排序类型
set @strTmp = ''
set @strOrder = ''
-- 只统计总记录数
if @IsCount != 0
begin
if @strWhere != ''
set @strTmp = " where (" + @strWhere + ")"
set @strSQL = " select count ( * ) as Total from [ " + @tblName + " ] " + @strTmp
end
-- 不统计总记录,分页(查询表中所有记录)
else
begin
-- 如果是第一页
if @PageIndex = 1
begin
-- 排序
if @OrderType != 0
set @strOrder = " order by [ " + @fldName +" ] desc "
else
set @strOrder = " order by [ " + @fldName +" ] asc "
-- 条件
if @strWhere != ''
set @strTmp = " where (" + @strWhere + ")"
set @strSQL = " select top " + str ( @PageSize ) + " " + @FieldsList + " from [ "
+ @tblName + " ] " + @strTmp + " " + @strOrder
end
-- 如果不是第一页
else
begin
-- 排序
if @OrderType != 0
begin
set @strTmp = " < ( select min "
set @strOrder = " order by [ " + @fldName +" ] desc "
end
else
begin
set @strTmp = " > ( select max "
set @strOrder = " order by [ " + @fldName +" ] asc "
end
set @strSQL = " select top " + str ( @PageSize ) + " " + @FieldsList + " from [ "
+ @tblName + " ] where [ " + @fldName + " ] " + @strTmp + "( [ "
+ @fldName + " ] ) from ( select top " + str (( @PageIndex - 1 ) * @PageSize ) + " [ "
+ @fldName + " ] from [ " + @tblName + " ] " + @strOrder + ") as tblTmp)"
+ @strOrder
-- 条件
if @strWhere != ''
set @strSQL = " select top " + str ( @PageSize ) + " " + @FieldsList + " from [ "
+ @tblName + " ] where [ " + @fldName + " ] " + @strTmp + "( [ "
+ @fldName + " ] ) from ( select top " + str (( @PageIndex - 1 ) * @PageSize ) + " [ "
+ @fldName + " ] from [ " + @tblName + " ] where (" + @strWhere + ") "
+ @strOrder + ") as tblTmp) and (" + @strWhere + ") " + @strOrder
end
end
exec ( @strSQL )