SQL Server Insert And Update the Tables to the SP

kumar k
Nov 26 2015
4k
0
0

ALTER Procedure[dbo].[Proc_InsertCandidateProfileDetails]
(
@CandidateAppliedJobId int,
@CandAdditional xml,
@CandAddress xml,
@CandApplied xml,
@CandQulification xml,
@CandExp xml,
@CandCertification xml
)
As
Begin
Set Nocount On
--CandidateAppliedJobDetails table
IF OBJECT_ID('tempdb..#tmpParametersJobDetails') IS NOT NULL
DROP TABLE# tmpParametersJobDetails;
SELECT
t.c.query('ID').value('.', 'int') AS ID,
t.c.query('Gender').value('.', 'int') AS Gender,
NULLIF(t.c.query('FirstName').value('.', 'varchar(100)'), '') AS FirstName,
NULLIF(t.c.query('MiddleName').value('.', 'varchar(20)'), '') AS MiddleName,
NULLIF(t.c.query('LastName').value('.', 'varchar(100)'), '') AS LastName,
NULLIF(t.c.query('FatherName').value('.', 'varchar(150)'), '') AS FatherName,
NULLIF(t.c.query('Email').value('.', 'varchar(100)'), '') AS Email,
NULLIF(t.c.query('Phone').value('.', 'varchar(25)'), '') AS Phone,
NULLIF(t.c.query('PANNumber').value('.', 'varchar(50)'), '') AS PANNumber,
NULLIF(t.c.query('PassportNo').value('.', 'varchar(50)'), '') AS PassportNo,
NULLIF(t.c.query('AadhaarNo').value('.', 'varchar(50)'), '') AS AadhaarNo,
NULLIF(t.c.query('DrivingLicense').value('.', 'varchar(25)'), '') AS DrivingLicense,
NULLIF(t.c.query('LanguagesKnown').value('.', 'varchar(100)'), '') AS LanguagesKnown,
NULLIF(t.c.query('DateOfBirth').value('.', 'date'), '') AS DateOfBirth,
NULLIF(t.c.query('ComputerKnowledge').value('.', 'varchar(20)'), '') AS ComputerKnowledge,
NULLIF(t.c.query('Speciality').value('.', 'varchar(20)'), '') AS Speciality,
NULLIF(t.c.query('Channel').value('.', 'int'), '') AS Channel,
NULLIF(t.c.query('ChannelDetails').value('.', 'varchar(20)'), '') AS ChannelDetails,
NULLIF(t.c.query('EmployeeCode').value('.', 'varchar(20)'), '') AS EmployeeCode,
NULLIF(t.c.query('MaritalStatus').value('.', 'int'), '') AS MaritalStatus,
NULLIF(t.c.query('BloodGroup').value('.', 'int'), '') AS BloodGroup, t.c.query('UserId').value('.', 'int') AS UserId
INTO# tmpParametersJobDetails
FROM @CandAdditional.nodes('/CandidateRegistrationModel1') AS t(c);
--CandidateSource
IF OBJECT_ID('tempdb..#tmpParametersSourceDetails') IS NOT NULL
DROP TABLE# tmpParametersSourceDetails;
SELECT
t.c.query('ID').value('.', 'int') AS ID,
NULLIF(t.c.query('Channel').value('.', 'int'), '') AS Channel,
NULLIF(t.c.query('Completed').value('.', 'bit'), '') AS Completed,
NULLIF(t.c.query('ChannelDetails').value('.', 'varchar(20)'), '') AS ChannelDetails,
NULLIF(t.c.query('EmployeeCode').value('.', 'varchar(20)'), '') AS EmployeeCode,
NULLIF(t.c.query('Consultant').value('.', 'varchar(20)'), '') AS Consultant,
NULLIF(t.c.query('Comments').value('.', 'varchar(2000)'), '') AS Comments,
NULLIF(t.c.query('HRStatus').value('.', 'int'), '') AS HRStatus,
NULLIF(t.c.query('CandidateType').value('.', 'int'), '') AS CandidateType, t.c.query('UserId').value('.', 'int') AS UserId, NULLIF(t.c.query('ResumePath').value('.', 'varchar(1000)'), '') AS ResumePath
INTO# tmpParametersSourceDetails
FROM @CandApplied.nodes('/CandidateAppliedJobDetails') AS t(c);
--AddressTable
IF OBJECT_ID('tempdb..#tmpParametersAddress') IS NOT NULL
DROP TABLE# tmpParametersAddress;
SELECT
t.c.query('ID').value('.', 'int') AS ID,
t.c.query('TempId').value('.', 'int') AS TempID,
t.c.query('PerId').value('.', 'int') AS PerID,
t.c.query('AddressType').value('.', 'int') AS AddressType,
NULLIF(t.c.query('Address1').value('.', 'varchar(100)'), '') AS Address1,
NULLIF(t.c.query('Address2').value('.', 'varchar(20)'), '') AS Address2,
NULLIF(t.c.query('City').value('.', 'varchar(20)'), '') AS City,
NULLIF(t.c.query('State').value('.', 'varchar(25)'), '') AS State,
NULLIF(t.c.query('PinCode').value('.', 'varchar(50)'), '') AS PinCode,
NULLIF(t.c.query('PerAddress1').value('.', 'varchar(100)'), '') AS PerAddress1,
NULLIF(t.c.query('PerAddress2').value('.', 'varchar(20)'), '') AS PerAddress2,
NULLIF(t.c.query('PerCity').value('.', 'varchar(20)'), '') AS PerCity,
NULLIF(t.c.query('PerState').value('.', 'varchar(25)'), '') AS PerState,
NULLIF(t.c.query('PerPinCode').value('.', 'varchar(50)'), '') AS PerPinCode, t.c.query('UserId').value('.', 'int') AS UserId
INTO# tmpParametersAddress
FROM @CandAddress.nodes('/CandidateRegisterAddressDetails') AS t(c);
--CandidateRegisterQualifications Table
IF OBJECT_ID('tempdb..#tmpParametersQualification') IS NOT NULL
DROP TABLE# tmpParametersQualification;
SELECT
t.c.query('ID').value('.', 'int') AS ID,
NULLIF(t.c.query('InstitutionName').value('.', 'varchar(100)'), '') AS InstitutionName,
NULLIF(t.c.query('Course').value('.', 'varchar(100)'), '') AS Course,
NULLIF(t.c.query('GradeOrClassOrPercentage').value('.', 'varchar(100)'), '') AS GradeOrClassOrPercentage,
NULLIF(t.c.query('YearOfPassing').value('.', 'int'), '') AS YearOfPassing,
NULLIF(t.c.query('PeriodFrom').value('.', 'varchar(100)'), '') AS PeriodFrom,
NULLIF(t.c.query('PeriodTo').value('.', 'varchar(100)'), '') AS PeriodTo,
NULLIF(t.c.query('HighestQualification').value('.', 'bit'), '') AS HighestQualification, t.c.query('UserId').value('.', 'int') AS UserId
INTO# tmpParametersQualification
FROM @CandQulification.nodes('/CandidateRegisterQualificationsList/CandidateRegisterQualifications') AS t(c);
--CandidateRegisterExperienceDetails Table
IF OBJECT_ID('tempdb..#tmpParametersExperience') IS NOT NULL
DROP TABLE# tmpParametersExperience;
SELECT
t.c.query('ID').value('.', 'int') AS ID,
NULLIF(t.c.query('CompanyName').value('.', 'varchar(100)'), '') AS CompanyName,
NULLIF(t.c.query('JobTitle').value('.', 'varchar(100)'), '') AS JobTitle,
NULLIF(t.c.query('ExperienceType').value('.', 'bit'), '') AS ExperienceType,
NULLIF(t.c.query('WorkPeriodFrom').value('.', 'varchar(100)'), '') AS WorkPeriodFrom,
NULLIF(t.c.query('WorkPeriodTo').value('.', 'varchar(100)'), '') AS WorkPeriodTo, t.c.query('UserId').value('.', 'int') AS UserId
INTO# tmpParametersExperience
FROM @CandExp.nodes('/CandidateRegisterExperienceDetailsList/CandidateRegisterExperienceDetails') AS t(c);
--CandidateRegisterCertificationDetails
IF OBJECT_ID('tempdb..#tmpParametersCertification') IS NOT NULL
DROP TABLE# tmpParametersCertification;
SELECT
t.c.query('ID').value('.', 'int') AS ID,
NULLIF(t.c.query('CertificateCategory').value('.', 'int'), '') AS CertificateCategory,
NULLIF(t.c.query('CertificateName').value('.', 'int'), '') AS CertificateName,
NULLIF(t.c.query('OtherCertificate').value('.', 'varchar(200)'), '') AS OtherCertificate,
NULLIF(t.c.query('CertifiedBody').value('.', 'varchar(100)'), '') AS CertifiedBody,
NULLIF(t.c.query('CertificationNo').value('.', 'varchar(100)'), '') AS CertificationNo,
NULLIF(t.c.query('MembershipID').value('.', 'varchar(100)'), '') AS MembershipID,
NULLIF(t.c.query('WithCorporatePlan').value('.', 'int'), '') AS WithCorporatePlan,
NULLIF(t.c.query('CertifiedDate').value('.', 'date'), '') AS CertifiedDate,
NULLIF(t.c.query('CertExpiryDate').value('.', 'date'), '') AS CertExpiryDate,
NULLIF(t.c.query('CertStatus').value('.', 'int'), '') AS CertStatus,
NULLIF(t.c.query('MembershipRenewedDate').value('.', 'date'), '') AS MembershipRenewedDate,
NULLIF(t.c.query('NextRenewalDate').value('.', 'date'), '') AS NextRenewalDate, t.c.query('UserId').value('.', 'int') AS UserId
INTO# tmpParametersCertification
FROM @CandCertification.nodes('/CandidateRegisterCertificationDetailsList/CandidateRegisterCertificationDetails') AS t(c);
--Update CandidateRegistrationDetails
Update a set
a.FirstName = b.FirstName, a.MiddleName = b.MiddleName,
a.FullName = RTRIM(LTRIM(ISNULL(b.FirstName, '') + isnull(' ' + b.MiddleName, '') + isnull(' ' + b.LastName, ''))),
a.LastName = b.LastName, a.Gender = b.Gender, a.FatherName = b.FatherName,
a.Email = b.Email, a.Phone = b.Phone, a.PANNumber = b.PANNumber,
a.PassportNo = b.PassportNo, a.AadhaarNo = b.AadhaarNo, a.DrivingLicense = b.DrivingLicense,
a.DateOfBirth = b.DateOfBirth,
a.ComputerKnowledge = b.ComputerKnowledge,
a.LanguagesKnown = b.[LanguagesKnown], a.[Speciality] = b.[Speciality], a.[MaritalStatus] = b.[MaritalStatus], a.[BloodGroup] = b.[BloodGroup],
a.LastUpdatedBy = b.UserId, a.LastUpdatedOn = Getdate()
From CandidateRegistrationDetails a Join# tmpParametersJobDetails b on a.ID = (select CandidateId from CandidateAppliedJobDetails where ID = @CandidateAppliedJobId)
--Update CandidateAppliedJobDetails
Declare @CompleteStatus bit
select CompleteStatus = (select Completed from CandidateAppliedJobDetails where ID = @CandidateAppliedJobId)
Update a set
a.[Channel] = b.[Channel],
a.[ChannelDetails] = b.[ChannelDetails],
a.Comments = b.Comments,
a.Completed = b.Completed,
a.[EmployeeCode] = b.[EmployeeCode],
a.[Consultant] = b.[Consultant],
a.HRStatus = isnull(b.HRStatus, 0),
a.LastUpdatedBy = b.UserId, a.LastUpdatedOn = Getdate()
From CandidateAppliedJobDetails a Join# tmpParametersSourceDetails b on a.ID = @CandidateAppliedJobId
--ADDRESS DETAILS
Update a set
a.AddressType = (select code from lookupdetails where[Description] = 'Temporary'), a.Address1 = b.Address1, a.Address2 = b.Address2, a.City = b.City, a.State = b.State, a.PinCode = b.PinCode,
a.LastUpdatedBy = b.UserId, a.LastUpdatedOn = Getdate()
From[CandidateRegisterAddressDetails] a Join# tmpParametersAddress b on a.ID = b.TempID
Update a set
a.AddressType = (select code from lookupdetails where[Description] = 'Permanent'), a.Address1 = b.PerAddress1, a.Address2 = b.PerAddress2, a.City = b.PerCity, a.State = b.PerState, a.PinCode = b.PerPinCode,
a.LastUpdatedBy = b.UserId, a.LastUpdatedOn = Getdate()
From[CandidateRegisterAddressDetails] a Join# tmpParametersAddress b on a.ID = b.PerID
Insert into[CandidateRegisterAddressDetails]
(
CandidateAppliedJobId, AddressType, Address1, Address2, City, State, PinCode, CreatedBy, CreatedOn
)
Select
@CandidateAppliedJobId as CandidateAppliedJobId, (select code from lookupdetails where[Description] = 'Temporary') as AddressType,
Address1, Address2, City, State, PinCode, UserId as CreatedBy, GetDate() as CreatedOn
From# tmpParametersAddress
Where ISNULL(TempID, 0) = 0
Insert into[CandidateRegisterAddressDetails]
(
CandidateAppliedJobId, AddressType, Address1, Address2, City, State, PinCode, CreatedBy, CreatedOn
)
Select
@CandidateAppliedJobId as CandidateAppliedJobId, (select code from lookupdetails where[Description] = 'Permanent') as AddressType,
PerAddress1 as Address1, PerAddress2 as Address2, PerCity as City, PerState as State, PerPinCode as PinCode, UserId as CreatedBy, GetDate() as CreatedOn
From# tmpParametersAddress
Where ISNULL(PerID, 0) = 0
--CandidateRegisterQualifications
Delete From[CandidateRegisterQualifications]
Where CandidateAppliedJobId = @CandidateAppliedJobId and
ID not in (select ID from# tmpParametersQualification)
Update a set
a.InstitutionName = b.InstitutionName, a.Course = b.Course, a.GradeOrClassOrPercentage = b.GradeOrClassOrPercentage,
a.YearOfPassing = b.YearOfPassing, a.PeriodFrom = b.PeriodFrom, a.PeriodTo = b.PeriodTo, a.HighestQualification = b.HighestQualification,
a.LastUpdatedBy = b.UserId, a.LastUpdatedOn = Getdate()
From[CandidateRegisterQualifications] a Join# tmpParametersQualification b on a.ID = b.ID
Insert Into[CandidateRegisterQualifications]
(
CandidateAppliedJobId, InstitutionName, Course, GradeOrClassOrPercentage, YearOfPassing, PeriodFrom, PeriodTo, HighestQualification, CreatedBy, CreatedOn
)
Select
@CandidateAppliedJobId as CandidateAppliedJobId, InstitutionName, Course, GradeOrClassOrPercentage, YearOfPassing, PeriodFrom, PeriodTo, HighestQualification, UserId as CreatedBy, GetDate() as CreatedOn
From# tmpParametersQualification
Where ISNULL(ID, 0) = 0
--CandidateRegisterExperienceDetails
Delete From[CandidateRegisterExperienceDetails]
Where CandidateAppliedJobId = @CandidateAppliedJobId and
ID not in (select ID from# tmpParametersExperience)
Update a set
a.CompanyName = b.CompanyName, a.JobTitle = b.JobTitle, a.ExperienceType = b.ExperienceType, a.WorkPeriodFrom = b.WorkPeriodFrom, a.WorkPeriodTo = b.WorkPeriodTo,
a.LastUpdatedBy = b.UserId, a.LastUpdatedOn = Getdate()
From[CandidateRegisterExperienceDetails] a Join# tmpParametersExperience b on a.ID = b.ID
Insert into[CandidateRegisterExperienceDetails]
(
CandidateAppliedJobId, CompanyName, JobTitle, ExperienceType, WorkPeriodFrom, WorkPeriodTo, CreatedBy, CreatedOn
)
Select
@CandidateAppliedJobId as CandidateAppliedJobId, CompanyName, JobTitle, ExperienceType, WorkPeriodFrom, WorkPeriodTo, UserId as CreatedBy, GetDate() as CreatedOn
From# tmpParametersExperience
Where ISNULL(ID, 0) = 0 and ExperienceType = 1
--CandidateRegisterCertificationDetails
Delete From[CandidateRegisterCertificationDetails]
Where CandidateAppliedJobId = @CandidateAppliedJobId and
ID not in (select ID from# tmpParametersCertification)
Update a set
a.CertificateCategory = b.CertificateCategory, a.CertifiedBody = b.CertifiedBody,
a.CertificationNo = b.CertificationNo, a.MembershipID = b.MembershipID, a.WithCorporatePlan = b.WithCorporatePlan,
a.CertifiedDate = b.CertifiedDate, a.CertExpiryDate = b.CertExpiryDate, a.CertStatus = b.CertStatus,
a.MembershipRenewedDate = b.MembershipRenewedDate, a.NextRenewalDate = b.NextRenewalDate,
a.LastUpdatedBy = b.UserId, a.LastUpdatedOn = Getdate()
From CandidateRegisterCertificationDetails a Join# tmpParametersCertification b on a.ID = b.ID
Update a set
a.CertificateName = b.CertificateName,
a.OtherCertificate = null
From CandidateRegisterCertificationDetails a Join# tmpParametersCertification b on a.ID = b.ID
Where b.CertificateCategory in (select Code from LookUpDetails where Description = 'Coders')
Update a set
a.OtherCertificate = b.OtherCertificate,
a.CertificateName = null
From CandidateRegisterCertificationDetails a Join# tmpParametersCertification b on a.ID = b.ID
Where b.CertificateCategory in (select Code from LookUpDetails where Description = 'Others')
insert into[CandidateRegisterCertificationDetails]
(
CandidateAppliedJobId, CertificateCategory, CertificateName, OtherCertificate, CertifiedBody, CertificationNo, MembershipID, WithCorporatePlan, CertifiedDate, CertExpiryDate, CertStatus, MembershipRenewedDate, NextRenewalDate, CreatedBy, CreatedOn
)
Select
@CandidateAppliedJobId as CandidateAppliedJobId, CertificateCategory, CertificateName, OtherCertificate, CertifiedBody, CertificationNo, MembershipID, WithCorporatePlan, CertifiedDate, CertExpiryDate, CertStatus, MembershipRenewedDate, NextRenewalDate, UserId as[CreatedBy], GetDate() as[CreatedOn]
From# tmpParametersCertification
Where ISNULL(ID, 0) = 0
--Mail Alert
if (@CompleteStatus = 0)
Begin
INSERT INTO[MailQueues](Module, ModuleId, UserId, ToMail, CCMail, BCCMail, CreatedOn, CreatedBy, Mailsend, Location)
Select c.CodeType as Module, b.ID as ModuleId, a.CreatedBy as UserId, d.MailTO as ToMail, d.MailCC as CCMail, d.MailBCC as BCCMail,
Getdate() as CreatedOn, a.CreatedBy, 0 Mailsend, b.JobLocation
From CandidateRegistrationDetails a(NOLOCK) join CandidateAppliedJobDetails b(NOLOCK) on a.ID = b.CandidateId
Join LookupDetails c(nolock) on c.Module = 'CANDIDATEREGISTRATION'
and c.CodeType = 'PROFILECOMPLETIONALERT'
Join RequestionConfigurations d(nolock) on c.Code = d.RequestType
Where b.ID = @CandidateAppliedJobId and b.completed = 1
End
Set Nocount OFF
End

Up Next