How To Avoid Duplicate Entries in SQL Server 2014 Using SP

Question

I have an online insurance application which is being used nationwide by less than 200 users. In rare cases, for some reason, while clicking the submit button, it saves the duplicate entry in my database. Below is the stored procedure of my insert statement:

 

ALTER PROCEDURE [dbo].[spInsertMotorInsuranceShortTerm_transaction]
    -- Add the parameters for the stored procedure here

          (
                @MotorInsuranceID int,
                @RefNumber nvarchar(15),
                @IssuanceType nvarchar(50),
                @CustomerNo nvarchar(50),
                @VehicleType nvarchar(50),
                @VehicleBrand nvarchar(50),
                @VehiclePlateNo nvarchar(50),
                @VehicleEngineNo nvarchar(50),
                @VehicleChasisNo nvarchar(50),
                @VehiclePower nvarchar(50),
                @InsuranceType nvarchar(50),
                @InsuranceOption nvarchar(50),
                @PeriodFrom date,
                @PeriodTo date,
                @ApplicationTime nvarchar(50),
                @DateIssued date,
                @NetPremium decimal(18,2),
                @RegistrationFee decimal(18,2),
                @VAT decimal(18,2),
                @TotalPremium decimal(18,2),
                @BranchCode varchar(50),
                @AgentID nvarchar(50),
                @Username varchar(50),
                @AgentGrossCommission decimal(18,2),
                @AgentTax decimal(18,2),
                @AgentNetCommission decimal(18,2),
                @ProductType varchar(50),
                @Seller nvarchar(50),
                @Remarks nvarchar(50),
                @CustomerID int,
                @ReferenceNo nvarchar(50),
                @CustomerName nvarchar(255),
                @TaxPayersID nvarchar(50),
                @Village nvarchar(50),
                @District nvarchar(50),
                @Province nvarchar(50),
                @MobilePhone nvarchar(50),
                @CustTelephone nvarchar(50),
                @CustEmail nvarchar(50),
                @Agent nvarchar(50),
                @Status nvarchar(50),
                @TrailDate nvarchar(50),
                @TrailTime nvarchar(50))

AS
BEGIN

    BEGIN TRY

        BEGIN TRANSACTION

            INSERT INTO [dbo].[tblMotorInsurance_eCI]
                       (
                        [RefNumber]
                       ,[IssuanceType]
                       ,[CustomerNo]
                       ,[VehicleType]
                       ,[VehicleBrand]
                       ,[VehiclePlateNo]
                       ,[VehicleEngineNo]
                       ,[VehicleChasisNo]
                       ,[VehiclePower]
                       ,[InsuranceType]
                       ,[InsuranceOption]
                       ,[PeriodFrom]
                       ,[PeriodTo]
                       ,[ApplicationTime]
                       ,[DateIssued]
                       ,[NetPremium]
                       ,[RegistrationFee]
                       ,[VAT]
                       ,[TotalPremium]
                       ,[BranchCode]
                       ,[AgentID]
                       ,[Username]
                       ,[AgentGrossCommission]
                       ,[AgentTax]
                       ,[AgentNetCommission]
                       ,[ProductType]
                       ,[Seller]
                       ,[Remarks]
                       )
                 VALUES
                       (
                       @RefNumber,
                       @IssuanceType,
                       @CustomerNo,
                       @VehicleType,
                       @VehicleBrand,
                       @VehiclePlateNo,
                       @VehicleEngineNo,
                       @VehicleChasisNo,
                       @VehiclePower,
                       @InsuranceType,
                       @InsuranceOption,
                       @PeriodFrom,
                       @PeriodTo,
                       @ApplicationTime,
                       @DateIssued,
                       @NetPremium,
                       @RegistrationFee,
                       @VAT,
                       @TotalPremium,
                       @BranchCode,
                       @AgentID,
                       @Username,
                       @AgentGrossCommission,
                       @AgentTax,
                       @AgentNetCommission,
                       @ProductType,
                       @Seller,
                       @Remarks
                       )

            IF NOT EXISTS (select CustomerNo from tblCustomers where CustomerNo=@CustomerNo)
            INSERT INTO [dbo].[tblCustomers]
                      (
                        [ReferenceNo]
                        ,[CustomerNo]
                        ,[CustomerName]
                        ,[TaxPayersID]
                        ,[Village]
                        ,[District]
                        ,[Province]
                        ,[CustTelephone]
                        ,[MobilePhone]
                        ,[CustEmail]
                        ,[AgentID]
                        ,[Status]
                        ,[Username]
                        ,[TrailDate]
                        ,[TrailTime])
                     VALUES
                     (
                         @ReferenceNo,
                         @CustomerNo,
                         @CustomerName,
                         @TaxPayersID,
                         @Village,
                         @District,
                         @Province,
                         @MobilePhone,
                         @CustTelephone,
                         @CustEmail,
                         @Agent,
                         @Status,
                         @Username,
                         @TrailDate,
                         @TrailTime)

        COMMIT TRANSACTION

    END TRY

    BEGIN CATCH
            ROLLBACK TRANSACTION
            RAISERROR('Duplicate record found. All entries are reverted to its original state.',16,1)
    END CATCH
END

Please note that I cannot make a condition for the first table like what I did in the second table.

 

Well, actually, it is not really duplicate. It's just that when they click the submit button, somehow it sends 2 entries in my database. 

 

Below is the duplicate entries: