Introduction

Here, we will see how to create select, insert, update, and delete statements using stored procedures in SQL Server. Let's take a look at a practical example. We create a table.

Create a Table in SQL

CREATE TABLE employee  
(  
    id         INTEGER NOT NULL PRIMARY KEY,  
    first_name VARCHAR(10),  
    last_name  VARCHAR(10),  
    salary     DECIMAL(10, 2),  
    city       VARCHAR(20),  
) 

Now add some rows to the table. We can add new rows using an INSERT INTO SQL statement. Then execute a SELECT SQL query to display all records of the table. 

INSERT INTO employee  
VALUES      (2,  
             'Monu',  
             'Rathor',  
             4789,  
             'Agra');  
  
go  
  
INSERT INTO employee  
VALUES      (4,  
             'Rahul',  
             'Saxena',  
             5567,  
             'London');  
go  
  
INSERT INTO employee  
VALUES      (5,  
             'prabhat',  
             'kumar',  
             4467,  
             'Bombay');
go  
  
INSERT INTO employee  
VALUES      (6,  
             'ramu',  
             'kksingh',  
             3456,  
             'jk');
go  
  
SELECT *  
FROM   employee

The table looks like this.

employeetable.gif

Stored Procedure for Select, Insert, Update, Delete

Here, we create a stored procedure with SELECT, INSERT, UPDATE, and DELETE SQL statements. The SELECT SQL statement is used to fetch rows from a database table. The INSERT statement is used to add new rows to a table. The UPDATE statement is used to edit and update the values of an existing record. The DELETE statement is used to delete records from a database table. The following SQL stored procedure is used insert, update, delete, and select rows from a table, depending on the statement type parameter. 

ALTER PROCEDURE Masterinsertupdatedelete (@id            INTEGER,  
                                          @first_name    VARCHAR(10),  
                                          @last_name     VARCHAR(10),  
                                          @salary        DECIMAL(10, 2),  
                                          @city          VARCHAR(20),  
                                          @StatementType NVARCHAR(20) = '')  
AS  
  BEGIN  
      IF @StatementType = 'Insert'  
        BEGIN  
            INSERT INTO employee  
                        (id,  
                         first_name,  
                         last_name,  
                         salary,  
                         city)  
            VALUES     ( @id,  
                         @first_name,  
                         @last_name,  
                         @salary,  
                         @city)  
        END  
  
      IF @StatementType = 'Select'  
        BEGIN  
            SELECT *  
            FROM   employee  
        END  
  
      IF @StatementType = 'Update'  
        BEGIN  
            UPDATE employee  
            SET    first_name = @first_name,  
                   last_name = @last_name,  
                   salary = @salary,  
                   city = @city  
            WHERE  id = @id  
        END  
      ELSE IF @StatementType = 'Delete'  
        BEGIN  
            DELETE FROM employee  
            WHERE  id = @id  
        END  
  END

Now press F5 to execute the stored procedure. This will create a new stored procedure in the database.

Now open object explorer and select store procedure MasterInsertUpdateDelete.

Stored Procedure to Check Insert

StatementType = 'Insert'

MasterInsertUpdateDelete -> right-click select Execute Stored Procedure.

employeetable1.gif

Execute procedure window will be opened.

employeetable3.gif

Now for insertion, we fill the data in values in the required fields.

StatementType=insert

employeetable4.gif

Click on the OK button.

You will see a new row added to the database table.

employeetable5.gif

Stored Procedure to Check update

MasterInsertUpdateDelete -> right-click select Execute Stored Procedure...

Execute procedure window will be opened.

StatementType = 'Update'

employeetable6.gif

Click on the OK button.

Check the employee table with the following updated data where the id is 7.

employeetable7.gif

Stored Procedure to Check Delete

MasterInsertUpdateDelete -> right-click select Execute Stored Procedure.

Execute procedure window will be opened.

StatementType = 'Delete'

employeetable8.gif

We will delete records from the table which has id=2.

Click on the OK button. And check in the employee table with the following deleted data where the id is 2.

employeetable9.gif

Summary

A single stored procedure can be used to select, add, update, and delete data from a database table. In this article, we learned how to create a single stored procedure to perform all operations using a single SP in SQL Server.