临时表循环插入

ALTER PROCEDURE [dbo].[Writelogin]
AS
BEGIN
DROP TABLE #temp --删除临时表
create table #temp --创建临时表
(
  id int identity(1,1), --主键ID
  Name nvarchar(50),--司机
  Num nvarchar(50), --编号
  NextExamDate DATETIME, --行驶证下次年检
  DrivrQualificationLimitedDate DATETIME, --从业资格证
  NextDDTDate DATETIME,--DDT培训
  NextPhysicalCheckDate DATETIME, --FT培训
  NextTiredTrainDate DATETIME -- 体检
)


--声明字段
declare @rows INT
declare @n  int
declare @driverName nvarchar(50)
declare @driverNum nvarchar(50)
declare @NextExamDate DATETIME
declare @DrivrQualificationLimitedDate DATETIME
declare @NextDDTDate DATETIME
declare @NextPhysicalCheckDate DATETIME
declare @NextTiredTrainDate DATETIME
declare @ErrorMessage nvarchar(1000)
select @n=1
SET @ErrorMessage=''  --复制初始变量 否则 NULL+字符串=Null

--插入符合条件的数据放到临时表
insert #temp(NAME,Num,NextExamDate,DrivrQualificationLimitedDate,NextDDTDate,NextPhysicalCheckDate,NextTiredTrainDate) select NAME,Num,NextExamDate,DrivrQualificationLimitedDate,NextDDTDate,NextPhysicalCheckDate,NextTiredTrainDate
  FROM Driver WHERE  [Deleted]=0  AND (CONVERT(varchar(100), GETDATE(), 102)> NextExamDate
OR CONVERT(varchar(100), GETDATE(), 102)> DrivrQualificationLimitedDate
OR CONVERT(varchar(100), GETDATE(), 102)> NextDDTDate
OR CONVERT(varchar(100), GETDATE(), 102)>NextPhysicalCheckDate
OR CONVERT(varchar(100), GETDATE(), 102)>NextTiredTrainDate)

select @rows = @@rowcount --查询表行数

while @n <= @rows --是否大于表行数
begin

select @driverName=[Name],@driverNum=Num,@NextExamDate=NextExamDate,@DrivrQualificationLimitedDate=DrivrQualificationLimitedDate,@NextDDTDate=NextDDTDate,@NextPhysicalCheckDate=NextPhysicalCheckDate,@NextTiredTrainDate=NextTiredTrainDate FROM #temp WHERE id=@n

IF(CONVERT(varchar(100), GETDATE(), 102)> @NextExamDate)
BEGIN
  set @ErrorMessage += '行驶证下次年检日过期;';
END

IF(CONVERT(varchar(100), GETDATE(), 102)> @DrivrQualificationLimitedDate)
BEGIN
 set @ErrorMessage +='从业资格证过期;';
END

IF(CONVERT(varchar(100), GETDATE(), 102)> @NextDDTDate)
BEGIN
 set @ErrorMessage +='DDT培训过期;';
END

IF(CONVERT(varchar(100), GETDATE(), 102)> @NextPhysicalCheckDate)
BEGIN
 set @ErrorMessage +='FT培训过期;';
END

IF(CONVERT(varchar(100), GETDATE(), 102)> @NextTiredTrainDate)
BEGIN
 set @ErrorMessage += '司机体检过期;';
END

SET @ErrorMessage=@driverNum+'-'+@driverName+' '+@ErrorMessage;
INSERT SysLogRecord(Cperator,IP,DEPT,[Content],YesOrNo,SystTme,LogType) values('System','','System',@ErrorMessage,1,getdate(),'系统处理');
select @n = @n + 1

end
END

转载于:https://www.cnblogs.com/liuruitao/p/4950516.html