SQL Server : Stored Procedure for Preventing Duplicated Values

Question

I have this code which I have created which I want to stop any duplicated values from being inserted into the database, however the values still continue to be able to get into the IF_Translations table. I have tried changing the 0 to a -1 on this line:

IF (SELECT COUNT(*) FROM IF_TRANSLATIONS WHERE INVALUE = @INVALUE) = 0

Any help?

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[translationduplicate]
AS
    SET NOCOUNT ON;

    DECLARE @ID INT, @TRANSLATE_ID VARCHAR(15), @INVALUE VARCHAR(15),
            @OUTVALUE VARCHAR(15), @CONV_DIRECTION VARCHAR(5), 
            @PLANT_ID VARCHAR(5), @LMOD DATETIME

    IF (SELECT COUNT(*) FROM IF_TRANSLATIONS WHERE INVALUE = @INVALUE) = 0
    BEGIN   
        INSERT INTO IF_TRANSLATIONS(ID, TRANSLATE_ID, INVALUE, OUTVALUE, CONV_DIRECTION, PLANT_ID)    
      VALUES((SELECT MAX(ID)FROM [OPERATOR_LIVE_43].[dbo].[IF_TRANSLATIONS]) + 1, @TRANSLATE_ID, @INVALUE, @OUTVALUE, 3, @PLANT_ID)
    END

`SELECT MAX(ID) + 1` - seriously: **do NOT do this!** This is bound to produce duplicates - if you need a unique counter, use an `INT IDENTITY` column or a `SEQUENCE` in SQL Server - that's the proper way to go — marc_s, Dec 27 '19 at 11:19
`IF NOT EXISTS (SELECT COUNT(*) FROM IF_TRANSLATIONS WHERE INVALUE = @INVALUE) ` something like this — Jaydip Jadhav, Dec 27 '19 at 11:20
Hi, The duplicate I want to prevent is the same INVALUE from being entered again. — glh, Dec 27 '19 at 11:21
You should add a unique constraint to that `INVALUE ` column to ensure uniqueness. And @JaydipJadhav - that is completely wrong. It doesn't address any of the race conditions and always evaluates to `false` anyway as a scalar aggregate always returns one row — Martin Smith, Dec 27 '19 at 11:22
@Milney - I see you deleted your answer. Just in case you still weren't convinced. See https://i.stack.imgur.com/yxBxD.png (running at default read committed in two connections in SQL Server 2019) - it stops almost immediately with a duplicate found — Martin Smith, Dec 27 '19 at 12:12

score 1 · Accepted Answer · answered Dec 27 '19 at 11:32

The correct solution is to let the database check the integrity. You would do this with a unique constraint or index (the two are equivalent for this purpose):

alter table if_translations add constraint unq_if_translations_invalue
    unique (invalue);

You should also fix the ID so it is an IDENTITY column -- incrementing the value in your code is not safe, unless you lock the entire table and that is quite expensive.

SQL Server : Stored Procedure for Preventing Duplicated Values

1 Answers1