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