0
Answer

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

eliza sahoo

eliza sahoo

14y
6.3k
1
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