Update Parent Name gHRM Plus

 

DECLARE --Put initial Selected data     

 @InitialRawData Table(    

  RowNum   int  IDENTITY(1,1) NOT NULL ,     

  EmployeeCode   nvarchar(200) , 

  MotherName nVarchar(300) NULL    

  --MotherName nVarchar(300) NULL ,    

  --SpouseName nVarchar (300) NULL ,  

  --Height nVarchar(300) ,

  -- Blood nvarchar(20)

 )     

 

INSERT INTO @InitialRawData    

SELECT      

EmployeeCode, [Mothers 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), @CurrMotherName nvarchar(20), @Gender nvarchar(10)  

SELECT @CurrEmployeeCode =  EmployeeCode, @CurrMotherName = MotherName FROM @InitialRawData Where RowNum = @LeaveSELLRow

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

 

--F

--M

IF EXISTS

(

    SELECT *

    FROM  EmployeeFamilyInfo

    WHERE EmployeeId = @EmpId AND Relation = 'M'

)

BEGIN -- Update

         Update EmployeeFamilyInfo SET Name = @CurrMotherName WHERE EmployeeId = @EmpId AND Relation = 'M'

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, @CurrMotherName, 'M',      'F',      NULL,                 NULL,              NULL,        1,        NULL,      1,             0,          NULL,                NULL,              3,    '2018-07-11 12:53:00', NULL, NULL

)

END

SET @LeaveSELLRow += 1    

END