CODEWITHSUNDEEP
sqlsql-serverstored-procedures
glh
glh

Reputation: 31

SQL Server : Stored Procedure for Preventing Duplicated Values

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

Upvotes: 0

Views: 80

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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.

Upvotes: 1

Related Questions