Staging table Loop Insertion

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.