ALTER PROCEDURE [dbo]. [Writelogin]
As
BEGIN
DROP table #temp--Delete temporary table
CREATE TABLE #temp--Creating a temporary table
(
ID int identity (+),--primary Key ID
Name nvarchar (,--driver)
Num nvarchar (50),--Number
Nextexamdate DATETIME,--driving permit next annual inspection
Drivrqualificationlimiteddate DATETIME,--Qualification certificate
Nextddtdate DATETIME,--DDT training
Nextphysicalcheckdate DATETIME,--ft Training
Nexttiredtraindate DATETIME--Physical examination
)
--Declaration fields
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 = '--Copy the initial variable otherwise null+ string =null
--inserting qualifying data into a temporary table
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), GETDATE (), 102) > Nextexamdate
OR CONVERT (varchar), GETDATE (), 102) > Drivrqualificationlimiteddate
OR CONVERT (varchar), GETDATE (), 102) > Nextddtdate
OR CONVERT (varchar (+), GETDATE (), 102) >nextphysicalcheckdate
OR CONVERT (varchar (+), GETDATE (), 102) >nexttiredtraindate)
Select @rows = @ @rowcount--query table row count
While @n <= @rows--is greater than the number of table rows
Begin
Select @driverName =[name], @driverNum =num, @NextExamDate =nextexamdate, @DrivrQualificationLimitedDate = Drivrqualificationlimiteddate, @NextDDTDate =nextddtdate, @NextPhysicalCheckDate =nextphysicalcheckdate,@ Nexttiredtraindate=nexttiredtraindate from #temp WHERE [email protected]
IF (CONVERT (varchar), GETDATE (), 102) > @NextExamDate)
BEGIN
Set @ErrorMessage + = ' Driving certificate expires on the next annual inspection day; ';
END
IF (CONVERT (varchar), GETDATE (), 102) > @DrivrQualificationLimitedDate)
BEGIN
Set @ErrorMessage + = ' Qualification certificate expired; ';
END
IF (CONVERT (varchar), GETDATE (), 102) > @NextDDTDate)
BEGIN
Set @ErrorMessage + = ' DDT training expires; ';
END
IF (CONVERT (varchar), GETDATE (), 102) > @NextPhysicalCheckDate)
BEGIN
Set @ErrorMessage + = ' ft training expires; ';
END
IF (CONVERT (varchar), GETDATE (), 102) > @NextTiredTrainDate)
BEGIN
Set @ErrorMessage + = ' driver physical examination expires; ';
END
SET @[email protected]+ '-' [email protected]+ ' [email protected];
INSERT Syslogrecord (Cperator,ip,dept,[content],yesorno,systtme,logtype) VALUES (' System ', ' ', ' system ', @ Errormessage,1,getdate (), ' system processing ');
Select @n = @n + 1
End
END
Staging table Loop Insertion