Update Stored Procedure with exist and insert

 USE [PMS]

GO

/****** Object:  StoredProcedure [dbo].[addleavedetails]    Script Date: 02-12-2022 11:39:13 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[addleavedetails]

  @LeaveType nvarchar(100),

  @StartDate datetime,

  @EndDate datetime,

  @fmsid int,

  @id int

  as

  begin

  

  if (@id='')

  begin

  if not exists(select* from icms_leavedetails where fmsid=@fmsid and leavetype=@LeaveType and flag=1)begin

  insert into icms_leavedetails(LeaveType,flag,StartDate,EndDate,fmsid) values(@LeaveType,1,@StartDate,@EndDate,@fmsid)

  end

  else if (@LeaveType='EOL')

  begin 

  insert into icms_leavedetails(LeaveType,flag,StartDate,EndDate,fmsid) values(@LeaveType,1,@StartDate,@EndDate,@fmsid)

  end

  end

  else

  begin

  

  update  icms_leavedetails set LeaveType=@LeaveType,StartDate=@StartDate,EndDate=@EndDate where fmsid=@fmsid and id=@id

  end

 

  

  --if @id='True'

  --begin

  --update icms_TransferDrive set DefaultDrive='0' where positiontype='S'

  --update icms_TransferDrive set DefaultDrive='1' where ID=(select top 1 ID from icms_TransferDrive where positiontype='S' order by DateTime desc)

  --end

  end

-------------------------------------or ------------------------------

USE [PMS]

GO

/****** Object:  StoredProcedure [dbo].[proc_transfer_request]    Script Date: 19-12-2022 11:46:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[proc_transfer_request]

@FMSID bigint,

@MinTenurePts int,

@SpousePts smallint,

@MedicalPts smallint,

@MutualPts smallint,

@Reason varchar(500),

@AdminPts int,

@TotalPts int,

@DetailReason varchar(750),

@IpAddress varchar(130),

@TransferDriveID int,

@RemoteAreaDoc varchar(500),

@SpouseDoc varchar(500),

@MedicalDoc varchar(500),

@AdminApproval bit,

@Accomplishment nvarchar(500),

@NewContibution nvarchar(500),

@UnderTakingDoc nvarchar(500),

@GradePts int,

@MedicalbyGOVT nvarchar(20),

@MedicalCertificateDate datetime,

@NumberOfAwards int

as

begin

declare @ID bigint

set @ID=0

select top 1 @ID=ID from transfer_request where FMSID=@FMSID and Transfer_DriveID=@TransferDriveID order by ID desc

if(@ID>0)

begin

update transfer_request set Reason=@Reason, MinTenurePts=@MinTenurePts,SpousePts=@SpousePts,MedicalPts=@MedicalPts,TotalPts=@TotalPts,AdminPts=@AdminPts,DetailReason=@DetailReason,@RemoteAreaDoc=@RemoteAreaDoc,SpouseDocument=@SpouseDoc,MedicalDocument=@MedicalDoc,@IpAddress=@IpAddress,Submitted='false',DateTime=GETDATE(),AdminApproval=@AdminApproval,Accomplishment=@Accomplishment,NewContibution=@NewContibution,UnderTakingDoc=@UnderTakingDoc,GradePts=@GradePts,MutualPts=@MutualPts,MedicalbyGOVT = @MedicalbyGOVT, MedicalCertificateDate = @MedicalCertificateDate, NumberOfAwards=@NumberOfAwards where ID=@ID and FMSID=@FMSID

end

else

begin

insert into transfer_request(FMSID,MinTenurePts,SpousePts,MedicalPts,MutualPts,GradePts,TotalPts,Reason,DetailReason,Accomplishment,NewContibution,IpAddress,submitted,Transfer_DriveID,RemoteAreaDocument,SpouseDocument,MedicalDocument,UnderTakingDoc,IsTransferred,AdminApproval,DateTime,MedicalbyGOVT, MedicalCertificateDate, NumberOfAwards) values(@FMSID,@MinTenurePts,@SpousePts,@MedicalPts,@MutualPts,@GradePts,@TotalPts,@Reason,@DetailReason,@Accomplishment,@NewContibution,@IpAddress,'false',@TransferDriveID,@RemoteAreaDoc,@SpouseDoc,@MedicalDoc,@UnderTakingDoc,'false',@AdminApproval,GETDATE(),@MedicalbyGOVT, @MedicalCertificateDate, @NumberOfAwards)

end

end