Update Spouse Name gHRMPlus

 

DECLARE --Put initial Selected data     

 @InitialRawData Table(    

  RowNum   int  IDENTITY(1,1) NOT NULL ,     

  EmployeeCode   nvarchar(200) , 

  SpouseName nVarchar(300) NULL    

  --MotherName nVarchar(300) NULL ,    

  --SpouseName nVarchar (300) NULL ,  

  --Height nVarchar(300) ,

  -- Blood nvarchar(20)

 )     

 

INSERT INTO @InitialRawData    

SELECT      

EmployeeCode, [Spouse Name] 

FROM  UpdateAdvice as ls    

    

 SELECT * FROM  @InitialRawData

DECLARE     @LeaveSELLRow INT    

     ,@CountLS INT 

 SET @LeaveSELLRow = 1    

SET @CountLS = (SELECT COUNT(*) FROM @InitialRawData)    

WHILE @LeaveSELLRow <= @CountLS - 1    

BEGIN  

Declare @EmpId bigint,  @CurrEmployeeCode nvarchar(200), @CurrSpouseName nvarchar(20), @Gender nvarchar(10)  

SELECT @CurrEmployeeCode =  EmployeeCode, @CurrSpouseName = SpouseName FROM @InitialRawData Where RowNum = @LeaveSELLRow

SELECT @Gender = Gender, @EmpId = EmployeeId FROM Employee WHERE EmployeeCode =  @CurrEmployeeCode

 

--F

--M

IF(@Gender = 'M')

BEGIN -- WIFE

IF EXISTS

(

    SELECT *

    FROM  EmployeeFamilyInfo

    WHERE EmployeeId = @EmpId AND Relation = 'W'

)

BEGIN -- Update

         Update EmployeeFamilyInfo SET Name = @CurrSpouseName WHERE EmployeeId = @EmpId AND Relation = 'W'

END

ELSE -- INSERT

BEGIN

Insert INTO EmployeeFamilyInfo

(

EmployeeId, Name, Relation, Gender, DateOfBirth, EducationalQualification, Occupation, IsActive, InActiveDate, IsApproved, IsRejected, ApprovedOrRejectedBy, ApprovalOrRejectDate, CreateUser, CreateDate, UpdateUser, UpdateDate

)

Values

( 

 @EmpId, @CurrSpouseName, 'W',      'F',      NULL,                 NULL,              NULL,        1,        NULL,      1,             0,          NULL,                NULL,              3,    '2018-07-11 12:53:00', NULL, NULL

)

 

END

END

ELSE -- Husband

BEGIN 

 --SELECT 4246 - 3764 Rows

IF EXISTS

(

    SELECT *

    FROM  EmployeeFamilyInfo

    WHERE EmployeeId = @EmpId AND Relation = 'H'

)

BEGIN -- Update

         Update EmployeeFamilyInfo SET Name = @CurrSpouseName WHERE EmployeeId = @EmpId AND Relation = 'H'

END

ELSE -- INSERT

BEGIN

Insert INTO EmployeeFamilyInfo

(

EmployeeId, Name, Relation, Gender, DateOfBirth, EducationalQualification, Occupation, IsActive, InActiveDate, IsApproved, IsRejected, ApprovedOrRejectedBy, ApprovalOrRejectDate, CreateUser, CreateDate, UpdateUser, UpdateDate

)

Values

( 

 @EmpId, @CurrSpouseName, 'H',      'M',      NULL,                 NULL,              NULL,        1,        NULL,      1,             0,          NULL,                NULL,              3,    '2018-07-11 12:53:00', NULL, NULL

)

END

END

SET @LeaveSELLRow += 1    

END