SQL Server: Restrict the duplicate insertion of records to the database using Store Procedure

Question

In SQL Server Database we can check the record before insert to the database and insert the record to the database by creating a store procedure as follows:

 

 

CREATE PROCEDURE usp_AddTestData_UsingExists

(

      @Name varchar(50),

      @Qualification varchar(50)

) 

AS

DECLARE @Result int

BEGIN TRANSACTION 

IF EXISTS

(

      SELECT

            NULL

     FROM

            TestData WITH (UPDLOCK)

      WHERE

            [Name] = @Name AND

            Qualification = @Qualification

)

      BEGIN

            SELECT @Result = -1

      END

ELSE

      BEGIN

           INSERT INTO

               TestData

               (

                   [Name],

                   Qualification

               )

           VALUES

           (

                 @Name,

                 @Qualification

           )

           SELECT @Result = @@ERROR

     END

IF @Result <> 0

     BEGIN

            ROLLBACK

      END

ELSE

      BEGIN

            COMMIT

      END

RETURN @Result