TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
C# Corner
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
SQL Server Insert And Update the Tables to the SP
kumar k
Nov 26
2015
Code
3.9
k
0
0
facebook
twitter
linkedIn
Reddit
WhatsApp
Email
Bookmark
expand
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
SQL Server
Insert And Update the tables
SP