添加与删除用一个表统计总数后再分页在海量数据必须这么做，你那么做只对一半--读取总记录数
SET ROWCOUNT 1
SELECT @Count = Count
FROM BaseInfo_TheEmployee_Count
if @@ROWCOUNT <> 1
begin
set @Return = -6
return -6
end
if @@error <> 0
begin
set @Return = -7
return -7
end
--计算总页数
set @OutPage = dbo.CountPage(@Count, @SetPageRows)
--页码大于总页码侧修饰为数据总数计算分页页码
if @SetPage > @OutPage set @SetPage = @OutPage
--计算对后一页余数值
set @PageMin = dbo.CountTopMin(@Count,@SetPageRows,@SetPage,@OutPage)
--计算页码
set @PageMax = (@SetPage * @SetPageRows)
--读取员工信息与一寸相片与身份证复印件
if @ShowPhoto = 1 and @ShowIDCardPhoto = 1
if @Order = 1
begin
--重新设定读取行数
SET ROWCOUNT @PageMin
SELECT TOP (@PageMin) TheEmployeeID, TheEmployeeName, CreatedDate, Auditing,
UniqueNumber, Sex, Tel, Number, EnterDutyDate, DateOfBirth,
EducateTheBackgroundTypeID, HouseholdRegister, IDCard, TheEmployeeTypeID,
MobilePhone, StandbyTel, Address, State, Email, FixedWage, ZipCode, Remark,
SalaryAccountNumber, DepartmentTypeID, Piecework, BusinessMember, HeadshipTypeID,
DimissionState, IDCardPhoto, IDCardPhotoImageType, Photo, PhotoImageType
FROM (SELECT TOP (@PageMax) TheEmployeeID, TheEmployeeName, CreatedDate, Auditing,
UniqueNumber, Sex, Tel, Number, EnterDutyDate, DateOfBirth,
EducateTheBackgroundTypeID, HouseholdRegister, IDCard,
TheEmployeeTypeID, MobilePhone, StandbyTel, Address, State, Email,
FixedWage, ZipCode, Remark, SalaryAccountNumber, DepartmentTypeID,
Piecework, BusinessMember, HeadshipTypeID, DimissionState, IDCardPhoto,
IDCardPhotoImageType, Photo, PhotoImageType
FROM (SELECT BaseInfo_TheEmployee.TheEmployeeID,
BaseInfo_TheEmployee.TheEmployeeName,
BaseInfo_TheEmployee.CreatedDate, BaseInfo_TheEmployee.Auditing,
BaseInfo_TheEmployee.UniqueNumber, BaseInfo_TheEmployee.Sex,
BaseInfo_TheEmployee.Tel, BaseInfo_TheEmployee.Number,
BaseInfo_TheEmployee.EnterDutyDate,
BaseInfo_TheEmployee.DateOfBirth,
BaseInfo_TheEmployee.EducateTheBackgroundTypeID,
BaseInfo_TheEmployee.HouseholdRegister,
BaseInfo_TheEmployee.IDCard,
BaseInfo_TheEmployee.TheEmployeeTypeID,
BaseInfo_TheEmployee.MobilePhone,
BaseInfo_TheEmployee.StandbyTel, BaseInfo_TheEmployee.Address,
BaseInfo_TheEmployee.State, BaseInfo_TheEmployee.Email,
BaseInfo_TheEmployee.FixedWage, BaseInfo_TheEmployee.ZipCode,
BaseInfo_TheEmployee.Remark,
BaseInfo_TheEmployee.SalaryAccountNumber,
BaseInfo_TheEmployee.DepartmentTypeID,
BaseInfo_TheEmployee.Piecework,
BaseInfo_TheEmployee.BusinessMember,
BaseInfo_TheEmployee.HeadshipTypeID,
BaseInfo_TheEmployee.DimissionState,
BaseInfo_TheEmployee_IDCardPhoto.IDCardPhoto,
BaseInfo_TheEmployee_IDCardPhoto.IDCardPhotoImageType,
BaseInfo_TheEmployee_Photo.Photo,
BaseInfo_TheEmployee_Photo.PhotoImageType
FROM BaseInfo_TheEmployee INNER JOIN
BaseInfo_TheEmployee_IDCardPhoto ON
BaseInfo_TheEmployee.TheEmployeeID = BaseInfo_TheEmployee_IDCardPhoto.TheEmployeeID
INNER JOIN
BaseInfo_TheEmployee_Photo ON
BaseInfo_TheEmployee.TheEmployeeID = BaseInfo_TheEmployee_Photo.TheEmployeeID)
AS a
ORDER BY TheEmployeeID) AS b
ORDER BY TheEmployeeID DESC
if @@ROWCOUNT <> @PageMin
begin
set @Return = -8
return -8
end
if @@error <> 0
begin
set @Return = -9
return -9
end
set @Return = @PageMin
return @PageMin
end
回复 引用