How To Avoid Duplicate Entries in SQL Server 2014 Using SP

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:

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ravi Kiran Chanduri8y ago
I think there is an issue with your query,

You are not throwing an error when a duplicate record has found.
You should rollback the changes in the else case for the IF NOT EXISTS (select CustomerNo from tblCustomers where CustomerNo=@CustomerNo) Statement.
Naveen Kumar8y ago
Hi,

Better try to have a unique column in your table so that you can avoid the duplicate entry.

Regards,
Naveen
siddhartha sharna8y ago
Have you checked the Exec procedure directly? Please execute Procedure directly in SQL Server. and let me know there are still duplicate entries or not .

If you did not got duplicate entries then please use sql profilier for no of calling the procedure .