SQLTeam.com | Weblogs | Forums

Prevent Duplication key Not Working

tsql
sql2014

#1

Hi All,

I have the follow stored procedure:
ALTER PROCEDURE [dbo].[spUpdateOrInsert]
@ID int,
@value int
AS
BEGIN
SET NOCOUNT ON;

BEGIN TRAN
IF EXISTS (select 1 from table1 where ID = @ID )
BEGIN
UPDATE table1
SET Value = @value
WHERE ID=@ID
END
ELSE
BEGIN
INSERT INTO table1 (ID, Value)
VALUES (@ID, @Value)
END
COMMIT TRAN

END

in additional I have a NON-Clustered Unique on the ID Column.
I wrapped the procedure by transaction and there is a IF clause that say if it's exists than update, else INSERT.

Now, when calling the procedure From time to timeת I'm getting an exception that say: can't insert duplicate value in to table1.

Please advise,

Thanks,
S


#2

Maybe this will help you:


#3

Thanks a lot for Sharing.
very good article and solutions

Best,
S


#4