19
OctSQL Server Insert, Retrieve, Update, Delete Operations using Stored Procedures
SQL Server Insert, Retrieve, Update, Delete Operations using Stored Procedures: An Overview
In the database, you need to Insert, Update and Delete. In this SQL Server Tutorial, if we want to make a reliable and high-performance system, then these four operations must be implemented by the stored procedure in SQL. In this SQL Server Course, we will learn that stored procedures not only provide a way to execute a set of SQL statements but also play a crucial role in preventing SQL Injection attacks and reducing network traffic. For more about stored procedure refer to the article Stored Procedure Plan Recompilation and Performance Tuning.
Read More - Top DBMS Interview Questions and Answers
Insert Operation
We can insert records into the table(s) using the stored procedure by passing data in input parameters. The below code is used to insert a record in the table "Employee" using the stored procedure in SQL
CREATE TABLE Employee
(
EmpID int primary key, Name varchar(50),
Salary int,
Address varchar(100)
)
This SQL code generates an "Employee" table containing columns for employee ID (the primary key in SQL Server), name, salary, & address.
Insert into Employee(EmpID,Name,Salary,Address) Values(1,'Mohan',16000,'Delhi')
Insert into Employee(EmpID,Name,Salary,Address) Values(2,'Asif',15000,'Delhi')
Insert into Employee(EmpID,Name,Salary,Address) Values(3,'Bhuvnesh',19000,'Noida')
--See table
SELECT * FROM Employee
The code adds three employee records to a database called "Employee" with their ID, name, salary, and address, then gets and shows all of the data in that table.
CREATE PROCEDURE usp_InsertEmployee
@flag bit output,-- return 0 for fail,1 for success
@EmpID int,
@Name varchar(50),
@Salary int,
@Address varchar(100)
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
Insert into Employee(EmpID,Name,Salary,Address) Values(@EmpID,@Name,@Salary,@Address)
set @flag=1;
IF @@TRANCOUNT > 0
BEGIN commit TRANSACTION;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN rollback TRANSACTION;
END
set @flag=0;
END CATCH
END
This code generates a reusable procedure called "usp_InsertEmployee" that inserts employee data into the Employee database while handling potential issues and returning a success/failure flag.
--Execute above created procedure to insert rows into table
Declare @flag bit
EXEC usp_InsertEmployee @flag output,1,'Deepak',14000,'Noida'
if @flag=1
print 'Successfully inserted'
else
print 'There is some error'
To add a new employee, the code calls the "usp_InsertEmployee" operation, checks the returned success/failure flag, and outputs an instructive message.
--Execute above created procedure to insert rows into table
Declare @flag bit
EXEC usp_InsertEmployee @flag output,4,'Deepak',14000,'Noida'
if @flag=1
print 'Successfully inserted'
else
print 'There is some error'
The code does a pre-programmed procedure to add a new employee, checks to see if it was successful, and then informs the user of the outcome.
--now see modified table
Select * from Employee
The code retrieves and shows all of the data from the changed Employee database, allowing you to see the most recent changes.
Retrieve Operation
We can retrieve data from one or more tables/views in SQL with the help of join, using a stored procedure. We can put multiple SQL statements within a single stored procedure. The below code is used to fetch data from a table "Employee" using a stored procedure in SQL
-- first we Insert data in the table
Insert into Employee(EmpID,Name,Salary,Address) Values(1,'Mohan',16000,'Delhi')
Insert into Employee(EmpID,Name,Salary,Address) Values(2,'Asif',15000,'Delhi')
Insert into Employee(EmpID,Name,Salary,Address) Values(3,'Bhuvnesh',19000,'Noida')
go
--Now we create a procedure to fetch data
CREATE PROCEDURE usp_SelectEmployee
As
Select * from Employee ORDER By EmpID
The code first inserts three employee records into the "Employee" database, then creates a reusable function called "usp_SelectEmployee" to conveniently extract all employee data from that dataset, organized by their EmpID.
--Execute above created procedure to fetch data
exec usp_SelectEmployee
It executes the pre-defined operation "usp_SelectEmployee" to retrieve employee data, most likely in a readable manner.
Update Operation
We can update records of the table(s) using the stored procedure by passing data in input parameters. The below code is used to update a table "Employee" using a stored procedure in SQL
CREATE PROCEDURE usp_UpdateEmployee
@flag bit output,-- return 0 for fail,1 for success
@EmpID int,
@Salary int,
@Address varchar(100)
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
Update Employee set Salary=@Salary, Address=@Address
Where EmpID=@EmpID
set @flag=1;
IF @@TRANCOUNT > 0
BEGIN commit TRANSACTION;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN rollback TRANSACTION;
END
set @flag=0;
END CATCH
END
This code generates a reusable procedure called "usp_UpdateEmployee" that modifies an employee's pay and address in the Employee database while handling problems and returns a success/failure flag.
--Execute above created procedure to update table
Declare @flag bit
EXEC usp_UpdateEmployee @flag output,1,22000,'Noida'
if @flag=1 print 'Successfully updated'
else
print 'There is some error'
The code executes a pre-defined procedure to update an employee's salary and address, checks to see whether it was successful, and then informs the user of the outcome.
--now see updated table
Select * from Employee
The code collects and shows all data from the Employee database, allowing you to see the most recent information when an employee's pay and address have been updated. This allows you to check the update's success and inspect the updated record.
Delete Operation
We can delete records of the table(s) using the stored procedure by passing data in input parameters. The below code is used to update a table "Employee" using a stored procedure
CREATE PROCEDURE usp_DeleteEmployee
@flag bit output,-- return 0 for fail,1 for success
@EmpID int
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
Delete from Employee Where EmpID=@EmpID set @flag=1;
IF @@TRANCOUNT > 0
BEGIN commit TRANSACTION;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN rollback TRANSACTION;
END
set @flag=0;
END CATCH
END
This code generates a reusable method called "usp_DeleteEmployee" that deletes an employee from the Employee database while handling issues and returning a success/failure flag.
--Execute above created procedure to delete rows from table
Declare @flag bit
EXEC usp_DeleteEmployee @flag output, 4
if @flag=1
print 'Successfully deleted'
else
print 'There is some error'
The code executes a pre-programmed method to remove a specific employee (with EmpID 4) from the database, checks to see if it was successful, and then tells the user of the conclusion.
--now see modified table
Select * from Employee
The code gets and displays all of the data from the Employee database that has been deleted recently, allowing you to check the changes and inspect the revised contents.
Note
In a stored procedure we use an output parameter to return multiple values.
Generally, we use the output parameter in a stored procedure to get the status of the operation as I used the above "@flag" output parameter to get the status of the operation and whether these are successfully executed or not.
Read More:
Summary
For SQL Server database operations such as Insert Procedure in SQL, Retrieve Operation in SQL, Update Operation in SQL, and Delete Procedure, stored procedures improve dependability, performance, security, and maintainability. They are reusable, gracefully manage mistakes, and help to reduce network traffic.
FAQs
Q1. How can I retrieve data in SQL from a stored procedure?
Q2. Which method is used to perform INSERT UPDATE and DELETE operations?
Q3. How to UPDATE data in the table using stored procedure?
Q4. How do you retrieve data from a procedure?
Q5. How to update SQL stored procedure?
Take our Sqlserver skill challenge to evaluate yourself!
In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.