Avoid inserting duplicate records in SQL database table using stored procedure

Introduction

In this article we will learn how to avoid inserting duplicate records in SQL database table. There are many ways to accomplish this task, one of which is to use stored procedure.

SQL Stored procedure “sp_addEmpInfo”

CREATE PROCEDURE [dbo].[sp_addEmpInfo]
    @emp_id           int,
    @first_name       varchar(30),
    @last_name        varchar(30),
    @designation      varchar(40),
    @email            varchar(60),
    @address          varchar(100),
    @AlreadyIn        int = ''
AS
BEGIN
    -- First we need to check whether the email exists in the database table or not.
    SELECT @AlreadyIn = (SELECT COUNT(*) FROM tbl_emp WHERE email = @email)

    -- If the email exists, the select query will return 1 otherwise it will return 0.
    -- In this way, we can avoid inserting duplicate records.    
    IF(@AlreadyIn = 0)

        INSERT INTO tbl_emp
        (
            emp_id,
            first_name,
            last_name,
            designation,
            email,
            address
        )
        VALUES
        (
            @emp_id,
            @first_name,
            @last_name,
            @designation,
            @email,
            @address
        )
    
    -- Return the email existance status.
    SELECT @AlreadyIn 'emailExistanceStatus'
END

Execute stored procedure

EXEC sp_addEmpInfo '01', 'Mohammed', 'Imtiyaz', 'Engineer', 'imt@gmail.com', 'India'

Output