SQL 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

1. In a stored procedure we use an output parameter to return multiple values.

2. 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:

• Top SQL Interview Questions and Answers

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.

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.

GET FREE CHALLENGE