How to Create a Single Stored Procedure for All CRUD (Create, Read, Update, Delete) operations

 

What is a Stored Procedure?

A stored procedure is a precompiled collection of SQL statements that are kept in the database and may be called as needed. A stored procedure is a reusable programme that may be invoked by other programmes or scripts to reduce code duplication and improve performance.

Stored procedures can accept input parameters that are provided when the procedure is called and return output parameters or result sets that the caller application or script can utilize.

Stored procedures are often used to insert, update, delete, and select data as well as execute computations, transformations, and validations on the data.

Stored procedures can be used for a variety of purposes, such as:

• Reducing network traffic: Instead of sending multiple SQL statements to the server, you can send a single stored procedure call that executes multiple SQL statements on the server.
• Improving security: Stored procedures can be used to restrict access to sensitive data by controlling the access permissions to the procedure.
• Encapsulating business logic: By encapsulating complex business logic in a stored procedure, you can make your application more maintainable and scalable.

Writing a single stored procedure for insert, update, delete, and select operations can provide several benefits, like:

• Code Reusability: When a single stored procedure is used for multiple operations, it eliminates the need to write separate code for each operation. Developers can reuse the same code for different operations, which can save time and reduce errors.
• Improved Performance: Stored procedures are precompiled and stored in the database, which reduces the processing time required to execute them. By using a single stored procedure for multiple operations, the performance of the database can be improved as it eliminates the need to compile and store multiple procedures.
• Reduced Code Complexity: By using a single stored procedure, developers can reduce the complexity of their code. This makes it easier to maintain the code over time, and reduces the risk of introducing errors into the code when modifications are made.
• Better Security: Stored procedures can be executed with appropriate permissions and can provide an additional layer of security by restricting access to sensitive data. By using a single stored procedure for multiple operations, security can be better managed as access to the data can be controlled through a single point.Consistency: Using a single stored procedure for multiple operations ensures consistency in the database. This means that all data changes are made using the same logic, which reduces the chance of inconsistencies or errors in the database.

Here's an example stored procedure that can perform Insert, Update, Delete, and Select operations on the "Employee" table based on an input parameter named "@mode":

SQL
CREATE PROCEDURE [dbo].[InsertOrUpdateOrDeleteEmployee]
    @mode varchar(10),
    @ID int = null,
    @Code nvarchar(20),
    @Name nvarchar(100),
    @Location nvarchar(20),
    @Designation nvarchar(100),
    @Team nvarchar(50),
    @Status nvarchar(30),
    @Manager nvarchar(100),
    @DOB date,
    @DOJ date
AS
BEGIN
    SET NOCOUNT ON;

    IF @mode = 'INSERT'
    BEGIN
        INSERT INTO [dbo].[Employee] (
            [Code],
            [Name],
            [Location],
            [Designation],
            [Team],
            [Status],
            [Manager],
            [DOB],
            [DOJ]
        ) VALUES (
            @Code,
            @Name,
            @Location,
            @Designation,
            @Team,
            @Status,
            @Manager,
            @DOB,
            @DOJ
        );
    END
    ELSE IF @mode = 'UPDATE'
    BEGIN
        UPDATE [dbo].[Employee] SET
            [Code] = @Code,
            [Name] = @Name,
            [Location] = @Location,
            [Designation] = @Designation,
            [Team] = @Team,
            [Status] = @Status,
            [Manager] = @Manager,
            [DOB] = @DOB,
            [DOJ] = @DOJ
        WHERE [ID] = @ID;
    END
    ELSE IF @mode = 'DELETE'
    BEGIN
        DELETE FROM [dbo].[Employee] WHERE [ID] = @ID;
    END
    ELSE IF @mode = 'SELECT'
    BEGIN
        SELECT * FROM [dbo].[Employee] WHERE [ID] = @ID;
    END
END

This stored procedure takes an input parameter called "@mode", which is used to determine whether to perform an Insert, Update, Delete, or Select operation on the "Employee" table. If the mode is "INSERT", the stored procedure inserts a new row into the table using the provided values. If the mode is "UPDATE", the stored procedure updates an existing row in the table based on the "ID" value. If the mode is "DELETE", the stored procedure deletes an existing row from the table based on the "ID" value. If the mode is "SELECT", the stored procedure selects the row from the table based on the "ID" value. Note that the "ID" column is used as the primary key for the "Employee" table.

How to execute for Insert, Update, Delete and Select:

here are the examples of executing the "InsertOrUpdateOrDeleteEmployee" stored procedure for the Insert, Update, Delete, and Select operations using T-SQL commands:

1. Insert operation:

To insert a new employee record, you can execute the following T-SQL command:

sql
EXEC InsertOrUpdateOrDeleteEmployee 'Insert', NULL, 'ABC123', 'John Doe', 'New York', 'Developer', 'Dev Team', 'Active', 'Jane Smith', '1990-01-01', '2022-03-07'

In the above command, the first parameter specifies the mode of operation ("Insert"), followed by the input parameter values in the order defined in the stored procedure. The second parameter is set to NULL because it is an identity column and will be auto-generated.

2. Update operation:

To update an employee record, you can execute the following T-SQL command:

sql
EXEC InsertOrUpdateOrDeleteEmployee 'Update', 1, 'ABC123', 'John Doe', 'New York', 'Developer', 'Dev Team', 'Active', 'Jane Smith', '1990-01-01', '2022-03-07'

In the above command, the first parameter specifies the mode of operation ("Update"), followed by the input parameter values in the order defined in the stored procedure. The second parameter specifies the ID of the employee record to be updated.

3. Delete operation:

To delete an employee record, you can execute the following T-SQL command:

sql
EXEC InsertOrUpdateOrDeleteEmployee 'Delete', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL

In the above command, the first parameter specifies the mode of operation ("Delete"), followed by the ID of the employee record to be deleted. The rest of the input parameters are set to NULL because they are not required for the "Delete" operation.

4. Select operation:

To select employee records based on a specific criterion, you can execute the following T-SQL command:

sql
EXEC InsertOrUpdateOrDeleteEmployee 'Select', NULL, NULL, NULL, 'New York', NULL, NULL, NULL, NULL, NULL, NULL

In the above command, the first parameter specifies the mode of operation ("Select"), followed by the input parameter values in the order defined in the stored procedure. The fourth parameter specifies the location criterion for selecting employee records. The rest of the input parameters are set to NULL because they are not required for the "Select" operation.