9
Answers

How to prevent insert duplicate in SQL Server

Hold On

Hold On

7y
336
1
I have a stored procedure to insert data into 2 tables. I uses begin and commit transaction. But my if statement before the 2nd insert is not working. Now I have a duplicate entry not only on the first table but as well as on the 2nd table. Below is my stored procedure and hopefully someone can assist me.
  1. USE [DhipayaHQDB_Test]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[spInsertMotorInsuranceShortTerm_transaction]    Script Date: 8/9/2017 4:19:46 PM ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. -- =============================================  
  9. -- Author:      <Author,,Name>  
  10. -- Create date: <Create Date,,  
  11. -- Description: <Description,,  
  12. -- =============================================  
  13. ALTER PROCEDURE [dbo].[spInsertMotorInsuranceShortTerm_transaction]  
  14.     -- Add the parameters for the stored procedure here  
  15.           
  16.           (  
  17.                 @MotorInsuranceID int,  
  18.                 @RefNumber nvarchar(15),  
  19.                 @IssuanceType nvarchar(50),  
  20.                 @CustomerNo nvarchar(50),  
  21.                 @VehicleType nvarchar(50),  
  22.                 @VehicleBrand nvarchar(50),  
  23.                 @VehiclePlateNo nvarchar(50),  
  24.                 @VehicleEngineNo nvarchar(50),  
  25.                 @VehicleChasisNo nvarchar(50),  
  26.                 @VehiclePower nvarchar(50),  
  27.                 @InsuranceType nvarchar(50),  
  28.                 @InsuranceOption nvarchar(50),  
  29.                 @PeriodFrom date,  
  30.                 @PeriodTo date,  
  31.                 @ApplicationTime nvarchar(50),  
  32.                 @DateIssued date,  
  33.                 @NetPremium decimal(18,2),  
  34.                 @RegistrationFee decimal(18,2),  
  35.                 @VAT decimal(18,2),  
  36.                 @TotalPremium decimal(18,2),  
  37.                 @BranchCode varchar(50),  
  38.                 @AgentID nvarchar(50),  
  39.                 @Username varchar(50),  
  40.                 --@TransactionNumber nvarchar(50),  
  41.                 @AgentGrossCommission decimal(18,2),  
  42.                 @AgentTax decimal(18,2),  
  43.                 @AgentNetCommission decimal(18,2),  
  44.                 @ProductType varchar(50),  
  45.                 @Seller nvarchar(50),  
  46.                 @Remarks nvarchar(50),  
  47.                   
  48.                 --customer area  
  49.                 @CustomerID int,  
  50.                 @ReferenceNo nvarchar(50),  
  51.                 @CustomerName nvarchar(255),  
  52.                 @TaxPayersID nvarchar(50),  
  53.                 @Village nvarchar(50),  
  54.                 @District nvarchar(50),  
  55.                 @Province nvarchar(50),  
  56.                 @MobilePhone nvarchar(50),  
  57.                 @CustTelephone nvarchar(50),  
  58.                 @CustEmail nvarchar(50),  
  59.                 @Agent nvarchar(50),  
  60.                 @Status nvarchar(50),  
  61.                 @TrailDate nvarchar(50),  
  62.                 @TrailTime nvarchar(50))  
  63.       
  64. AS  
  65. BEGIN  
  66.       
  67.     BEGIN TRY  
  68.   
  69.         BEGIN TRANSACTION  
  70.   
  71.             -- insert statement for tblMotorInsurance_eCI   
  72.               
  73.             INSERT INTO [dbo].[tblMotorInsurance_eCI]  
  74.                        (  
  75.                         [RefNumber]  
  76.                        ,[IssuanceType]  
  77.                        ,[CustomerNo]  
  78.                        ,[VehicleType]  
  79.                        ,[VehicleBrand]  
  80.                        ,[VehiclePlateNo]  
  81.                        ,[VehicleEngineNo]  
  82.                        ,[VehicleChasisNo]  
  83.                        ,[VehiclePower]  
  84.                        ,[InsuranceType]  
  85.                        ,[InsuranceOption]  
  86.                        ,[PeriodFrom]  
  87.                        ,[PeriodTo]  
  88.                        ,[ApplicationTime]  
  89.                        ,[DateIssued]  
  90.                        ,[NetPremium]  
  91.                        ,[RegistrationFee]  
  92.                        ,[VAT]  
  93.                        ,[TotalPremium]  
  94.                        ,[BranchCode]  
  95.                        ,[AgentID]  
  96.                        ,[Username]  
  97.                        --,[TransactionNumber]  
  98.                        ,[AgentGrossCommission]  
  99.                        ,[AgentTax]  
  100.                        ,[AgentNetCommission]  
  101.                        ,[ProductType]  
  102.                        ,[Seller]  
  103.                        ,[Remarks]  
  104.                        )  
  105.                  VALUES  
  106.                        (  
  107.                          @RefNumber,   
  108.                        @IssuanceType,   
  109.                        @CustomerNo,   
  110.                        @VehicleType,   
  111.                        @VehicleBrand,   
  112.                        @VehiclePlateNo,   
  113.                        @VehicleEngineNo,   
  114.                        @VehicleChasisNo,   
  115.                        @VehiclePower,   
  116.                        @InsuranceType,   
  117.                        @InsuranceOption,   
  118.                        @PeriodFrom,   
  119.                        @PeriodTo,   
  120.                        @ApplicationTime,  
  121.                        @DateIssued,   
  122.                        @NetPremium,   
  123.                        @RegistrationFee,   
  124.                        @VAT,   
  125.                        @TotalPremium,   
  126.                        @BranchCode,   
  127.                        @AgentID,   
  128.                        @Username,   
  129.                        --@TransactionNumber,  
  130.                        @AgentGrossCommission,  
  131.                        @AgentTax,  
  132.                        @AgentNetCommission,  
  133.                        @ProductType,  
  134.                        @Seller,  
  135.                        @Remarks  
  136.                        )  
  137.   
  138.             -- insert statement for tblcustomers_shortterm   
  139.           
  140.             if exists (select * from tblCustomers_ShortTerm where CustomerNo=@CustomerNo)  
  141.             begin  
  142.                 INSERT INTO [dbo].[tblCustomers_ShortTerm]  
  143.                       (  
  144.                         [ReferenceNo]  
  145.                         ,[CustomerNo]  
  146.                         ,[CustomerName]  
  147.                         ,[TaxPayersID]  
  148.                         ,[Village]  
  149.                         ,[District]  
  150.                         ,[Province]  
  151.                         ,[CustTelephone]  
  152.                         ,[MobilePhone]  
  153.                         ,[CustEmail]  
  154.                         ,[AgentID]  
  155.                         ,[Status]  
  156.                         ,[Username]  
  157.                         ,[TrailDate]  
  158.                         ,[TrailTime])  
  159.                      VALUES  
  160.                      (  
  161.                          @ReferenceNo,  
  162.                          @CustomerNo,  
  163.                          @CustomerName,  
  164.                          @TaxPayersID,  
  165.                          @Village,  
  166.                          @District,  
  167.                          @Province,  
  168.                          @MobilePhone,  
  169.                          @CustTelephone,  
  170.                          @CustEmail,  
  171.                          @Agent,  
  172.                          @Status,  
  173.                          @Username,  
  174.                          @TrailDate,  
  175.                          @TrailTime)  
  176.             end   
  177.               
  178.         COMMIT TRANSACTION  
  179.   
  180.     END TRY  
  181.   
  182.     BEGIN CATCH  
  183.   
  184.         ROLLBACK TRANSACTION  
  185.         RAISERROR('An error occured. All entries are reverted to it original state.',16,1)  
  186.   
  187.     END CATCH  
  188. END  
Thanks in advance. 

Answers (9)