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.
No comments:
Post a Comment