SQL Server Insert And Update the Tables to the SP

  1. ALTER Procedure[dbo].[Proc_InsertCandidateProfileDetails]  
  2.     (  
  3.         @CandidateAppliedJobId int,  
  4.         @CandAdditional xml,  
  5.         @CandAddress xml,  
  6.         @CandApplied xml,  
  7.         @CandQulification xml,  
  8.         @CandExp xml,  
  9.         @CandCertification xml  
  10.     )  
  11. As  
  12. Begin  
  13. Set Nocount On  
  14. --CandidateAppliedJobDetails table  
  15. IF OBJECT_ID('tempdb..#tmpParametersJobDetails'IS NOT NULL  
  16. DROP TABLE# tmpParametersJobDetails;  
  17. SELECT  
  18. t.c.query('ID').value('.''int'AS ID,  
  19.     t.c.query('Gender').value('.''int'AS Gender,  
  20.     NULLIF(t.c.query('FirstName').value('.''varchar(100)'), ''AS FirstName,  
  21.     NULLIF(t.c.query('MiddleName').value('.''varchar(20)'), ''AS MiddleName,  
  22.     NULLIF(t.c.query('LastName').value('.''varchar(100)'), ''AS LastName,  
  23.     NULLIF(t.c.query('FatherName').value('.''varchar(150)'), ''AS FatherName,  
  24.     NULLIF(t.c.query('Email').value('.''varchar(100)'), ''AS Email,  
  25.     NULLIF(t.c.query('Phone').value('.''varchar(25)'), ''AS Phone,  
  26.     NULLIF(t.c.query('PANNumber').value('.''varchar(50)'), ''AS PANNumber,  
  27.     NULLIF(t.c.query('PassportNo').value('.''varchar(50)'), ''AS PassportNo,  
  28.     NULLIF(t.c.query('AadhaarNo').value('.''varchar(50)'), ''AS AadhaarNo,  
  29.     NULLIF(t.c.query('DrivingLicense').value('.''varchar(25)'), ''AS DrivingLicense,  
  30.     NULLIF(t.c.query('LanguagesKnown').value('.''varchar(100)'), ''AS LanguagesKnown,  
  31.     NULLIF(t.c.query('DateOfBirth').value('.''date'), ''AS DateOfBirth,  
  32.     NULLIF(t.c.query('ComputerKnowledge').value('.''varchar(20)'), ''AS ComputerKnowledge,  
  33.     NULLIF(t.c.query('Speciality').value('.''varchar(20)'), ''AS Speciality,  
  34.     NULLIF(t.c.query('Channel').value('.''int'), ''AS Channel,  
  35.     NULLIF(t.c.query('ChannelDetails').value('.''varchar(20)'), ''AS ChannelDetails,  
  36.     NULLIF(t.c.query('EmployeeCode').value('.''varchar(20)'), ''AS EmployeeCode,  
  37.     NULLIF(t.c.query('MaritalStatus').value('.''int'), ''AS MaritalStatus,  
  38.     NULLIF(t.c.query('BloodGroup').value('.''int'), ''AS BloodGroup, t.c.query('UserId').value('.''int'AS UserId  
  39. INTO# tmpParametersJobDetails  
  40. FROM @CandAdditional.nodes('/CandidateRegistrationModel1'AS t(c);  
  41. --CandidateSource  
  42. IF OBJECT_ID('tempdb..#tmpParametersSourceDetails'IS NOT NULL  
  43. DROP TABLE# tmpParametersSourceDetails;  
  44. SELECT  
  45. t.c.query('ID').value('.''int'AS ID,  
  46.     NULLIF(t.c.query('Channel').value('.''int'), ''AS Channel,  
  47.     NULLIF(t.c.query('Completed').value('.''bit'), ''AS Completed,  
  48.     NULLIF(t.c.query('ChannelDetails').value('.''varchar(20)'), ''AS ChannelDetails,  
  49.     NULLIF(t.c.query('EmployeeCode').value('.''varchar(20)'), ''AS EmployeeCode,  
  50.     NULLIF(t.c.query('Consultant').value('.''varchar(20)'), ''AS Consultant,  
  51.     NULLIF(t.c.query('Comments').value('.''varchar(2000)'), ''AS Comments,  
  52.     NULLIF(t.c.query('HRStatus').value('.''int'), ''AS HRStatus,  
  53.     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  
  54. INTO# tmpParametersSourceDetails  
  55. FROM @CandApplied.nodes('/CandidateAppliedJobDetails'AS t(c);  
  56. --AddressTable  
  57. IF OBJECT_ID('tempdb..#tmpParametersAddress'IS NOT NULL  
  58. DROP TABLE# tmpParametersAddress;  
  59. SELECT  
  60. t.c.query('ID').value('.''int'AS ID,  
  61.     t.c.query('TempId').value('.''int'AS TempID,  
  62.     t.c.query('PerId').value('.''int'AS PerID,  
  63.     t.c.query('AddressType').value('.''int'AS AddressType,  
  64.     NULLIF(t.c.query('Address1').value('.''varchar(100)'), ''AS Address1,  
  65.     NULLIF(t.c.query('Address2').value('.''varchar(20)'), ''AS Address2,  
  66.     NULLIF(t.c.query('City').value('.''varchar(20)'), ''AS City,  
  67.     NULLIF(t.c.query('State').value('.''varchar(25)'), ''AS State,  
  68.     NULLIF(t.c.query('PinCode').value('.''varchar(50)'), ''AS PinCode,  
  69.     NULLIF(t.c.query('PerAddress1').value('.''varchar(100)'), ''AS PerAddress1,  
  70.     NULLIF(t.c.query('PerAddress2').value('.''varchar(20)'), ''AS PerAddress2,  
  71.     NULLIF(t.c.query('PerCity').value('.''varchar(20)'), ''AS PerCity,  
  72.     NULLIF(t.c.query('PerState').value('.''varchar(25)'), ''AS PerState,  
  73.     NULLIF(t.c.query('PerPinCode').value('.''varchar(50)'), ''AS PerPinCode, t.c.query('UserId').value('.''int'AS UserId  
  74. INTO# tmpParametersAddress  
  75. FROM @CandAddress.nodes('/CandidateRegisterAddressDetails'AS t(c);  
  76. --CandidateRegisterQualifications Table  
  77. IF OBJECT_ID('tempdb..#tmpParametersQualification'IS NOT NULL  
  78. DROP TABLE# tmpParametersQualification;  
  79. SELECT  
  80. t.c.query('ID').value('.''int'AS ID,  
  81.     NULLIF(t.c.query('InstitutionName').value('.''varchar(100)'), ''AS InstitutionName,  
  82.     NULLIF(t.c.query('Course').value('.''varchar(100)'), ''AS Course,  
  83.     NULLIF(t.c.query('GradeOrClassOrPercentage').value('.''varchar(100)'), ''AS GradeOrClassOrPercentage,  
  84.     NULLIF(t.c.query('YearOfPassing').value('.''int'), ''AS YearOfPassing,  
  85.     NULLIF(t.c.query('PeriodFrom').value('.''varchar(100)'), ''AS PeriodFrom,  
  86.     NULLIF(t.c.query('PeriodTo').value('.''varchar(100)'), ''AS PeriodTo,  
  87.     NULLIF(t.c.query('HighestQualification').value('.''bit'), ''AS HighestQualification, t.c.query('UserId').value('.''int'AS UserId  
  88. INTO# tmpParametersQualification  
  89. FROM @CandQulification.nodes('/CandidateRegisterQualificationsList/CandidateRegisterQualifications'AS t(c);  
  90. --CandidateRegisterExperienceDetails Table  
  91. IF OBJECT_ID('tempdb..#tmpParametersExperience'IS NOT NULL  
  92. DROP TABLE# tmpParametersExperience;  
  93. SELECT  
  94. t.c.query('ID').value('.''int'AS ID,  
  95.     NULLIF(t.c.query('CompanyName').value('.''varchar(100)'), ''AS CompanyName,  
  96.     NULLIF(t.c.query('JobTitle').value('.''varchar(100)'), ''AS JobTitle,  
  97.     NULLIF(t.c.query('ExperienceType').value('.''bit'), ''AS ExperienceType,  
  98.     NULLIF(t.c.query('WorkPeriodFrom').value('.''varchar(100)'), ''AS WorkPeriodFrom,  
  99.     NULLIF(t.c.query('WorkPeriodTo').value('.''varchar(100)'), ''AS WorkPeriodTo, t.c.query('UserId').value('.''int'AS UserId  
  100. INTO# tmpParametersExperience  
  101. FROM @CandExp.nodes('/CandidateRegisterExperienceDetailsList/CandidateRegisterExperienceDetails'AS t(c);  
  102. --CandidateRegisterCertificationDetails  
  103. IF OBJECT_ID('tempdb..#tmpParametersCertification'IS NOT NULL  
  104. DROP TABLE# tmpParametersCertification;  
  105. SELECT  
  106. t.c.query('ID').value('.''int'AS ID,  
  107.     NULLIF(t.c.query('CertificateCategory').value('.''int'), ''AS CertificateCategory,  
  108.     NULLIF(t.c.query('CertificateName').value('.''int'), ''AS CertificateName,  
  109.     NULLIF(t.c.query('OtherCertificate').value('.''varchar(200)'), ''AS OtherCertificate,  
  110.     NULLIF(t.c.query('CertifiedBody').value('.''varchar(100)'), ''AS CertifiedBody,  
  111.     NULLIF(t.c.query('CertificationNo').value('.''varchar(100)'), ''AS CertificationNo,  
  112.     NULLIF(t.c.query('MembershipID').value('.''varchar(100)'), ''AS MembershipID,  
  113.     NULLIF(t.c.query('WithCorporatePlan').value('.''int'), ''AS WithCorporatePlan,  
  114.     NULLIF(t.c.query('CertifiedDate').value('.''date'), ''AS CertifiedDate,  
  115.     NULLIF(t.c.query('CertExpiryDate').value('.''date'), ''AS CertExpiryDate,  
  116.     NULLIF(t.c.query('CertStatus').value('.''int'), ''AS CertStatus,  
  117.     NULLIF(t.c.query('MembershipRenewedDate').value('.''date'), ''AS MembershipRenewedDate,  
  118.     NULLIF(t.c.query('NextRenewalDate').value('.''date'), ''AS NextRenewalDate, t.c.query('UserId').value('.''int'AS UserId  
  119. INTO# tmpParametersCertification  
  120. FROM @CandCertification.nodes('/CandidateRegisterCertificationDetailsList/CandidateRegisterCertificationDetails'AS t(c);  
  121. --Update CandidateRegistrationDetails  
  122. Update a set  
  123. a.FirstName = b.FirstName, a.MiddleName = b.MiddleName,  
  124.     a.FullName = RTRIM(LTRIM(ISNULL(b.FirstName, '') + isnull(' ' + b.MiddleName, '') + isnull(' ' + b.LastName, ''))),  
  125.     a.LastName = b.LastName, a.Gender = b.Gender, a.FatherName = b.FatherName,  
  126.     a.Email = b.Email, a.Phone = b.Phone, a.PANNumber = b.PANNumber,  
  127.     a.PassportNo = b.PassportNo, a.AadhaarNo = b.AadhaarNo, a.DrivingLicense = b.DrivingLicense,  
  128.     a.DateOfBirth = b.DateOfBirth,  
  129.     a.ComputerKnowledge = b.ComputerKnowledge,  
  130.     a.LanguagesKnown = b.[LanguagesKnown], a.[Speciality] = b.[Speciality], a.[MaritalStatus] = b.[MaritalStatus], a.[BloodGroup] = b.[BloodGroup],  
  131.     a.LastUpdatedBy = b.UserId, a.LastUpdatedOn = Getdate()  
  132. From CandidateRegistrationDetails a Join# tmpParametersJobDetails b on a.ID = (select CandidateId from CandidateAppliedJobDetails where ID = @CandidateAppliedJobId)  
  133.     --Update CandidateAppliedJobDetails  
  134. Declare @CompleteStatus bit  
  135. select CompleteStatus = (select Completed from CandidateAppliedJobDetails where ID = @CandidateAppliedJobId)  
  136. Update a set  
  137. a.[Channel] = b.[Channel],  
  138.     a.[ChannelDetails] = b.[ChannelDetails],  
  139.     a.Comments = b.Comments,  
  140.     a.Completed = b.Completed,  
  141.     a.[EmployeeCode] = b.[EmployeeCode],  
  142.     a.[Consultant] = b.[Consultant],  
  143.     a.HRStatus = isnull(b.HRStatus, 0),  
  144.     a.LastUpdatedBy = b.UserId, a.LastUpdatedOn = Getdate()  
  145. From CandidateAppliedJobDetails a Join# tmpParametersSourceDetails b on a.ID = @CandidateAppliedJobId  
  146.     --ADDRESS DETAILS  
  147. Update a set  
  148. 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,  
  149.     a.LastUpdatedBy = b.UserId, a.LastUpdatedOn = Getdate()  
  150. From[CandidateRegisterAddressDetails] a Join# tmpParametersAddress b on a.ID = b.TempID  
  151. Update a set  
  152. 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,  
  153.     a.LastUpdatedBy = b.UserId, a.LastUpdatedOn = Getdate()  
  154. From[CandidateRegisterAddressDetails] a Join# tmpParametersAddress b on a.ID = b.PerID  
  155. Insert into[CandidateRegisterAddressDetails]  
  156.     (  
  157.         CandidateAppliedJobId, AddressType, Address1, Address2, City, State, PinCode, CreatedBy, CreatedOn  
  158.     )  
  159. Select  
  160. @CandidateAppliedJobId as CandidateAppliedJobId, (select code from lookupdetails where[Description] = 'Temporary'as AddressType,  
  161. Address1, Address2, City, State, PinCode, UserId as CreatedBy, GetDate() as CreatedOn  
  162. From# tmpParametersAddress  
  163. Where ISNULL(TempID, 0) = 0  
  164. Insert into[CandidateRegisterAddressDetails]  
  165.     (  
  166.         CandidateAppliedJobId, AddressType, Address1, Address2, City, State, PinCode, CreatedBy, CreatedOn  
  167.     )  
  168. Select  
  169. @CandidateAppliedJobId as CandidateAppliedJobId, (select code from lookupdetails where[Description] = 'Permanent'as AddressType,  
  170. PerAddress1 as Address1, PerAddress2 as Address2, PerCity as City, PerState as State, PerPinCode as PinCode, UserId as CreatedBy, GetDate() as CreatedOn  
  171. From# tmpParametersAddress  
  172. Where ISNULL(PerID, 0) = 0  
  173.     --CandidateRegisterQualifications  
  174. Delete From[CandidateRegisterQualifications]  
  175. Where CandidateAppliedJobId = @CandidateAppliedJobId and  
  176. ID not in (select ID from# tmpParametersQualification)  
  177. Update a set  
  178. a.InstitutionName = b.InstitutionName, a.Course = b.Course, a.GradeOrClassOrPercentage = b.GradeOrClassOrPercentage,  
  179.     a.YearOfPassing = b.YearOfPassing, a.PeriodFrom = b.PeriodFrom, a.PeriodTo = b.PeriodTo, a.HighestQualification = b.HighestQualification,  
  180.     a.LastUpdatedBy = b.UserId, a.LastUpdatedOn = Getdate()  
  181. From[CandidateRegisterQualifications] a Join# tmpParametersQualification b on a.ID = b.ID  
  182. Insert Into[CandidateRegisterQualifications]  
  183.     (  
  184.         CandidateAppliedJobId, InstitutionName, Course, GradeOrClassOrPercentage, YearOfPassing, PeriodFrom, PeriodTo, HighestQualification, CreatedBy, CreatedOn  
  185.     )  
  186. Select  
  187. @CandidateAppliedJobId as CandidateAppliedJobId, InstitutionName, Course, GradeOrClassOrPercentage, YearOfPassing, PeriodFrom, PeriodTo, HighestQualification, UserId as CreatedBy, GetDate() as CreatedOn  
  188. From# tmpParametersQualification  
  189. Where ISNULL(ID, 0) = 0  
  190.     --CandidateRegisterExperienceDetails  
  191. Delete From[CandidateRegisterExperienceDetails]  
  192. Where CandidateAppliedJobId = @CandidateAppliedJobId and  
  193. ID not in (select ID from# tmpParametersExperience)  
  194. Update a set  
  195. a.CompanyName = b.CompanyName, a.JobTitle = b.JobTitle, a.ExperienceType = b.ExperienceType, a.WorkPeriodFrom = b.WorkPeriodFrom, a.WorkPeriodTo = b.WorkPeriodTo,  
  196.     a.LastUpdatedBy = b.UserId, a.LastUpdatedOn = Getdate()  
  197. From[CandidateRegisterExperienceDetails] a Join# tmpParametersExperience b on a.ID = b.ID  
  198. Insert into[CandidateRegisterExperienceDetails]  
  199.     (  
  200.         CandidateAppliedJobId, CompanyName, JobTitle, ExperienceType, WorkPeriodFrom, WorkPeriodTo, CreatedBy, CreatedOn  
  201.     )  
  202. Select  
  203. @CandidateAppliedJobId as CandidateAppliedJobId, CompanyName, JobTitle, ExperienceType, WorkPeriodFrom, WorkPeriodTo, UserId as CreatedBy, GetDate() as CreatedOn  
  204. From# tmpParametersExperience  
  205. Where ISNULL(ID, 0) = 0 and ExperienceType = 1  
  206.     --CandidateRegisterCertificationDetails  
  207. Delete From[CandidateRegisterCertificationDetails]  
  208. Where CandidateAppliedJobId = @CandidateAppliedJobId and  
  209. ID not in (select ID from# tmpParametersCertification)  
  210. Update a set  
  211. a.CertificateCategory = b.CertificateCategory, a.CertifiedBody = b.CertifiedBody,  
  212.     a.CertificationNo = b.CertificationNo, a.MembershipID = b.MembershipID, a.WithCorporatePlan = b.WithCorporatePlan,  
  213.     a.CertifiedDate = b.CertifiedDate, a.CertExpiryDate = b.CertExpiryDate, a.CertStatus = b.CertStatus,  
  214.     a.MembershipRenewedDate = b.MembershipRenewedDate, a.NextRenewalDate = b.NextRenewalDate,  
  215.     a.LastUpdatedBy = b.UserId, a.LastUpdatedOn = Getdate()  
  216. From CandidateRegisterCertificationDetails a Join# tmpParametersCertification b on a.ID = b.ID  
  217. Update a set  
  218. a.CertificateName = b.CertificateName,  
  219.     a.OtherCertificate = null  
  220. From CandidateRegisterCertificationDetails a Join# tmpParametersCertification b on a.ID = b.ID  
  221. Where b.CertificateCategory in (select Code from LookUpDetails where Description = 'Coders')  
  222. Update a set  
  223. a.OtherCertificate = b.OtherCertificate,  
  224.     a.CertificateName = null  
  225. From CandidateRegisterCertificationDetails a Join# tmpParametersCertification b on a.ID = b.ID  
  226. Where b.CertificateCategory in (select Code from LookUpDetails where Description = 'Others')  
  227. insert into[CandidateRegisterCertificationDetails]  
  228.     (  
  229.         CandidateAppliedJobId, CertificateCategory, CertificateName, OtherCertificate, CertifiedBody, CertificationNo, MembershipID, WithCorporatePlan, CertifiedDate, CertExpiryDate, CertStatus, MembershipRenewedDate, NextRenewalDate, CreatedBy, CreatedOn  
  230.     )  
  231. Select  
  232. @CandidateAppliedJobId as CandidateAppliedJobId, CertificateCategory, CertificateName, OtherCertificate, CertifiedBody, CertificationNo, MembershipID, WithCorporatePlan, CertifiedDate, CertExpiryDate, CertStatus, MembershipRenewedDate, NextRenewalDate, UserId as[CreatedBy], GetDate() as[CreatedOn]  
  233. From# tmpParametersCertification  
  234. Where ISNULL(ID, 0) = 0  
  235.     --Mail Alert  
  236. if (@CompleteStatus = 0)  
  237.     Begin  
  238. INSERT INTO[MailQueues](Module, ModuleId, UserId, ToMail, CCMail, BCCMail, CreatedOn, CreatedBy, Mailsend, Location)  
  239. 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,  
  240.     Getdate() as CreatedOn, a.CreatedBy, 0 Mailsend, b.JobLocation  
  241. From CandidateRegistrationDetails a(NOLOCK) join CandidateAppliedJobDetails b(NOLOCK) on a.ID = b.CandidateId  
  242. Join LookupDetails c(nolock) on c.Module = 'CANDIDATEREGISTRATION'  
  243. and c.CodeType = 'PROFILECOMPLETIONALERT'  
  244. Join RequestionConfigurations d(nolock) on c.Code = d.RequestType  
  245. Where b.ID = @CandidateAppliedJobId and b.completed = 1  
  246. End  
  247. Set Nocount OFF  
  248. End