Holi Sale. Get upto 40% OFF on Job-oriented Training! Offer Ending in
D
H
M
S
Get Now
Browse Tutorials
SQL Server Insert, Retrieve, Update, Delete Operations using Stored Procedures

SQL Server Insert, Retrieve, Update, Delete Operations using Stored Procedures

14 Mar 2024
Advanced
251K Views
16 min read

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.

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 Operation Message

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.

Insert Operation 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.

Insert Operation Procedure

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

Insert Operation 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.

Insert Operation - 1 row added

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

Insert Operation - Modified Table

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.

Retrieve Operation

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

Retrieve Operation Table

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.

Update Operation Message

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

Update Operation

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

Update Operation Table

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.

Delete Operation

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

Delete Operation Message

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

Delete Operation Table

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:

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?

Make the method that defines and then opens the Currency table cursor first. Afterward, run a batch file that creates a local variable called "cursor," assigns the cursor to the variable, and then retrieves the rows from the cursor.

Q2. Which method is used to perform INSERT UPDATE and DELETE operations?

To issue DML statements (insert, remove, and update operations) against the database in C#, utilize the 'ExecuteNonQuery' function. The 'ExecuteNonQuery' method in ASP.Net must be used to issue any table statements.

Q3. How to UPDATE data in the table using stored procedure?

Here, we build a stored procedure using SQL statements for SELECT, INSERT, UPDATE, and DELETE. To retrieve rows from a database table, use the SELECT SQL statement. A table can have additional rows added to it using the INSERT statement. To modify and update the values of an existing record, use the UPDATE statement.

Q4. How do you retrieve data from a procedure?

By providing system parameters to your stored procedure, you can get data from your source table. 

Q5. How to update SQL stored procedure?

 To alter a process, expand Stored Procedures, right-click on it, and choose to alter. Change the saved procedure's text. Choose Parse from the Query menu to test the syntax. Click the Execute option on the Query menu to save the changes to the procedure definition.

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this