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

Here, we will see how to create select, insert, update, delete statements using stored procedure 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),
)

Now add some rows to the table. We can add new rows using an INSERT INTO SQL satement. 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

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 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 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 in 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 record from the table which has id=2.

Click on the OK Button. And check in the employee table with 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.