Tech
Forums
Jobs
Books
Events
Videos
Live
More
Interviews
Certification
Training
Career
Members
News
Blogs
Contribute
An Article
A Blog
A Video
An Ebook
An Interview Question
Register
Login
9
Answers
How to prevent insert duplicate in SQL Server
Hold On
7y
336
1
Reply
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.
USE [DhipayaHQDB_Test]
GO
/****** Object: StoredProcedure [dbo].[spInsertMotorInsuranceShortTerm_transaction] Script
Date
: 8/9/2017 4:19:46 PM ******/
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,
-- Description: <Description,,
-- =============================================
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),
--@TransactionNumber nvarchar(50),
@AgentGrossCommission
decimal
(18,2),
@AgentTax
decimal
(18,2),
@AgentNetCommission
decimal
(18,2),
@ProductType
varchar
(50),
@Seller nvarchar(50),
@Remarks nvarchar(50),
--customer area
@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 statement for tblMotorInsurance_eCI
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]
--,[TransactionNumber]
,[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,
--@TransactionNumber,
@AgentGrossCommission,
@AgentTax,
@AgentNetCommission,
@ProductType,
@Seller,
@Remarks
)
-- insert statement for tblcustomers_shortterm
if exists (
select
*
from
tblCustomers_ShortTerm
where
CustomerNo=@CustomerNo)
begin
INSERT
INTO
[dbo].[tblCustomers_ShortTerm]
(
[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)
end
COMMIT
TRANSACTION
END
TRY
BEGIN
CATCH
ROLLBACK
TRANSACTION
RAISERROR(
'An error occured. All entries are reverted to it original state.'
,16,1)
END
CATCH
END
Thanks in advance.
Post
Reset
Cancel
Answers (
9
)
Next Recommended Forum
how can we upload image in admin panel and show in front...?
Embedded image in Resource reported error ?