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

118 Views

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.

employeetable

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…

employeetable1

Figure2

Execute procedure window will be open.

employeetable3

Figure3

Now for insert we fill the data in required field.

StatementType=insert

employeetable4

Figure4

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

employeetable5

Figure5

Stored Procedure to Check update

MasterInsertUpdateDelete -> right click select execute stored procedure…

Execute procedure window will be open.

StatementType = ‘Update’

employeetable6

Figure6

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

employeetable7

Figure7

Stored Procedure to Check Delete

MasterInsertUpdateDelete -> right click select execute stored procedure…

Execute procedure window will be open.

StatementType = ‘Delete’

employeetable8

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.

employeetable9

Figure9

One comment

  1. Thanks for another fantastic article. Where else could anybody get that kind of information in such an ideal approach of writing? I’ve a presentation next week, and I am at the look for such information.

Leave a Reply

Your email address will not be published.

Skip to toolbar