SQL Server Stored Procedure - Check if Data Exists in Table Else Insert New Data

In this article, I'll walk you through the steps to check if data exists in a table and, if it does, how to seamlessly insert new data using a stored procedure.

Procedure to check if data already exists in table.

Let's assume that I have a table named dbo.Employee in SQL Server and I am using a Stored Procedure to insert data into this table. The table has the necessary "constraints" to prevent duplicate data from being inserted. However, I want to ensure that neither I or anyone else accidentally inserts duplicate data into the table.

So I can do a basic check in the stored procedure like this.

CREATE PROCEDURE dbo.InsertEmployee
    @EmpName NVARCHAR(255),
    @DOJ DATE
AS
BEGIN
    -- Check if the employee already exists.
    IF NOT EXISTS (SELECT 1 FROM dbo.Employee WHERE EmpName = @EmpName AND JoiningDate = @DOJ)
        BEGIN
            -- Insert the employee if they do not exist.
            INSERT INTO dbo.Employee (EmpName, JoiningDate)
            VALUES (@EmpName, @DOJ);
        END
    ELSE
        BEGIN
            -- Show a message.
            PRINT 'Employee already exists';
        END
END;
GO

The procedure takes two parameters, @EmpName (the employee's name) and @DOJ (the employee's date of joining).

The IF NOT EXISTS clause checks if an employee with the given name and date of joining already exists in the Employee table.

If the employee does not exist, the procedure inserts the employee's details into the table. It also prints a message.

Remember: If you are calling the stored procedure from an application like .NET or MS Excel, you'll need to utilize the application's messaging options to provide feedback or display results.

← PreviousNext →