Select, Insert, Update, Delete Using Stored Procedure in SQL Server

Introduction

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

Creating Table 

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

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
SQL

The table looks like this.

employeetable.gif

Figure 1

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
SQL

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

Now open object explorer and select storeprocedure MasterInsertUpdateDelete.

Stored Procedure to Check Insert

StatementType = 'Insert'

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

employeetable1.gif

Figure 2

Execute procedure window will be opened.

employeetable3.gif

Figure 3

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

StatementType=insert

employeetable4.gif

Figure 4

Click on the OK button. 

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

employeetable5.gif

Figure 5

Stored Procedure to Check update

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

Execute procedure window will be opened.

StatementType = 'Update'

employeetable6.gif

Figure 6

Click on the OK button.

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

employeetable7.gif

Figure 7

Stored Procedure to Check Delete

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

Execute procedure window will be opened.

StatementType = 'Delete'

employeetable8.gif

Figure 8

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 id is 2.

employeetable9.gif

Figure 9

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.

Press ESC to close