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. 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 insert some values in the table and using select statement to select a 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 for select,insert,update,delete statements to select the data from the table.

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.

Now open object explorer and select storeprocedure MasterInsertUpdateDelete.

Stored Procedure to Check Insert

StatementType = 'Insert'

MasterInsertUpdateDelete -> right click select execute stored procedure...

Figure2

Execute procedure window will be open.

Figure3

Now for insert we fill the data in required field.

StatementType=insert

Figure4

Click on the ok Button. and check in the employee table with following inserted data.

Figure5

Stored Procedure to Check update

MasterInsertUpdateDelete -> right click select execute stored procedure...

Execute procedure window will be open.

StatementType = 'Update'

Figure6

Click on the ok Button. and check in the employee table with following updated data where id is 7.

Figure7

Stored Procedure to Check Delete

MasterInsertUpdateDelete -> right click select execute stored procedure...

Execute procedure window will be open.

StatementType = 'Delete'

Figure8

we delete record from table which has id=2

Click on the ok Button. and check in the employee table with following deleted data where id is 2.

Figure9