Add If/Else statement to Stored Procedure

Transact-SQL
1

I have a SQL process, which involves ingesting an XML file, and either inserting the record or updating an existing record ([changetype] = 'I' or 'U)'. This record is then pulled into an external system, and updated to a doc management system (Processed gets set to the datetime and this runs every 15 minutes).

I use the below Stored Procedure to check if the record exists, and either insert the record if it doesn't exist (and set change type to 'i') or update the record if it does exists (and set changetype to 'u').

The problem is we sometimes get an insert and update record at the same time, and the update overwrites the insert. This stops the record from being ingested, as there is no existing record to update. What i'm thinking is having an If/Else statement, where If 'Processed IS NULL' it updates everything but sets changetype to 'i', Else it updates as it currently does. Not sure if that makes any sense.

IF NOT EXISTS (SELECT 1 FROM database Where ClaimReference = @ClaimReference)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO database (ClaimReference, CoverNumber, ClientNumber, ClientName, Department, Source, Consumer, SecurityGroup, RiskDescription, PolicyNumber, Insurer, PolicyDepartment, PolicyYear, Claimant, ClaimType, DateOfLoss, VehicleNumber, 
ChangeType, ClaimsHandler, DateofChange, Processed, ERROR, ERROR_MESSAGE) VALUES (@ClaimReference, @CoverNumber, @ClientNumber, @ClientName, @Department, @Consumer, @SecurityGroup, @RiskDescription, 
    @PolicyNumber, @Insurer, @PolicyDepartment, @PolicyYear, @Claimant, @ClaimType, @DateOfLoss, @VehicleNumber, 'i', @ClaimsHandler, GETDATE(), NULL, '0', NULL)

END
ELSE
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
UPDATE database
SET ClaimReference = @ClaimReference,
CoverNumber = @CoverNumber,
ClientNumber = @ClientNumber,
ClientName = @ClientName,
Department = @Department,
Consumer = @Consumer,
SecurityGroup = @SecurityGroup,
RiskDescription = @RiskDescription,
PolicyNumber = @PolicyNumber,
Insurer = @Insurer,
PolicyDepartment = @PolicyDepartment,
PolicyYear = @PolicyYear,
Claimant = @Claimant,
ClaimType = @ClaimType,
DateOfLoss = @DateOfLoss,
VehicleNumber = @VehicleNumber,
ChangeType = 'u',
ClaimsHandler = @ClaimsHandler,
DateofChange = GETDATE(),
Processed =NULL,
ERROR ='0',
ERROR_MESSAGE =NULL
WHERE ClaimReference = @ClaimReference

END

2

You could use a merge statement:

MERGE dbo.[database] t
USING 
   ( SELECT
         @ClaimReference
       , @CoverNumber
       , @ClientNumber
       , @ClientName
       , @Department
       , @Consumer
       , @SecurityGroup
       , @RiskDescription
       , @PolicyNumber
       , @Insurer
       , @PolicyDepartment
       , @PolicyYear
       , @Claimant
       , @ClaimType
       , @DateOfLoss
       , @VehicleNumber
       , 'i'
       , @ClaimsHandler
       , GETDATE()
       , NULL
       , '0'
       , NULL
       j
       ( ClaimReference
       , CoverNumber
       , ClientNumber
       , ClientName
       , Department
       , Source
       , Consumer
       , SecurityGroup
       , RiskDescription
       , PolicyNumber
       , Insurer
       , PolicyDepartment
       , PolicyYear
       , Claimant
       , ClaimType
       , DateOfLoss
       , VehicleNumber
       , ChangeType
       , ClaimsHandler
       , DateofChange
       , Processed
       , ERROR
       , [ERROR_MESSAGE]
       ) s ON t.ClaimReference = s.ClaimReference
WHEN MATCHED THEN UPDATE SET
   CoverNumber      = s.CoverNumber,
   ClientNumber     = s.ClientNumber,
   ClientName       = s.ClientName,
   Department       = s.Department,
   Consumer         = s.Consumer,
   SecurityGroup    = s.SecurityGroup,
   RiskDescription  = s.RiskDescription,
   PolicyNumber     = s.PolicyNumber,
   Insurer          = s.Insurer,
   PolicyDepartment = s.PolicyDepartment,
   PolicyYear       = s.PolicyYear,
   Claimant         = s.Claimant,
   ClaimType        = s.ClaimType,
   DateOfLoss       = s.DateOfLoss,
   VehicleNumber    = s.VehicleNumber,
   ChangeType       = s.ChangeType
   ClaimsHandler    = s.ClaimsHandler
   DateofChange     = s.DateofChange
   Processed        = s.Processed
   ERROR            = s.ERROR
   [ERROR_MESSAGE]  = s.[ERROR_MESSAGE]
WHEN NOT MATCHED THEN INSERT
   ( ClaimReference
   , CoverNumber
   , ClientNumber
   , ClientName
   , Department
   , Source
   , Consumer
   , SecurityGroup
   , RiskDescription
   , PolicyNumber
   , Insurer
   , PolicyDepartment
   , PolicyYear
   , Claimant
   , ClaimType
   , DateOfLoss
   , VehicleNumber
   , ChangeType
   , ClaimsHandler
   , DateofChange
   , Processed
   , ERROR
   , [ERROR_MESSAGE]
   )
VALUES
   ( s.ClaimReference
   , s.CoverNumber
   , s.ClientNumber
   , s.ClientName
   , s.Department
   , s.Source
   , s.Consumer
   , s.SecurityGroup
   , s.RiskDescription
   , s.PolicyNumber
   , s.Insurer
   , s.PolicyDepartment
   , s.PolicyYear
   , s.Claimant
   , s.ClaimType
   , s.DateOfLoss
   , s.VehicleNumber
   , s.ChangeType
   , s.ClaimsHandler
   , s.DateofChange
   , s.Processed
   , s.ERROR
   , s.[ERROR_MESSAGE]
   ) ;