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

 

This article was originally published on mesameergaikwad as Select, Insert, Update, Delete Using Stored Procedure in SQL Server.

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.

Creating Database

create database sameer 

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,
             'Sameer',
             'Gaikwad',
             4789,
             'Agra');

go

INSERT INTO employee
VALUES      (4,
             'Rahul',
             'Gupta',
             5567,
             'London');
go

INSERT INTO employee
VALUES      (5,
             'prabhat',
             'kumar',
             4467,
             'Bombay');
go

INSERT INTO employee
VALUES      (6,
             'ramu',
             'Khan',
             3456,
             'jk');
go

SELECT *
FROM   employee

SQL 

The table looks like this.

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...

Figure 2

Execute procedure window will be opened.

Figure 3

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

StatementType=insert

Figure 4

Click on the OK button. 

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

Figure 5

Stored Procedure to Check update

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

Execute procedure window will be opened.

StatementType = 'Update'

Figure 6

Click on the OK button.

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

Figure 7

Stored Procedure to Check Delete

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

Execute procedure window will be opened.

StatementType = 'Delete'

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.

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.