A stored procedure is a group of SQL statements that have been created and stored in the database. In this article, we will see how to select, insert, update and delete records (CRUD) or data using a single stored procedure in SQL Server. If we use stored procedures, the performance of the database will be improved. We can use stored procedures to Select, Insert, Update and Delete records from the database using SQL queries.

We will learn step by step instructions to perform the above-mentioned operations,

Step 1:

As a first step, we will create a table called "Employees" using the below query.

CREATE TABLE Employees  
(  
   Id INTEGER PRIMARY KEY,  
   FirstName VARCHAR(20),  
   LastName VARCHAR(20),  
   Age INT,  
   Gender VARCHAR(10),  
   Designation VARCHAR(20)
)  

Step 2:

We will insert records into the "Employees" table by using below INSERT statement.

Insert Employees Values (100, 'Sabari', 'M', 30, 'Male', 'Software Engg' );
Insert Employees Values (200, 'John', 'Peter', 35, 'Male', 'Manager' );
Insert Employees Values (300, 'Raj', 'Kumar', 25, 'Male', 'Tester' );
Insert Employees Values (400, 'Sophia', 'Raj', 30, 'Female', 'Business Analyst' );
Insert Employees Values (500, 'Naveen', 'P', 20, 'Male', 'Software Engg' );
Insert Employees Values (600, 'Emma', 'K', 28, 'Female', 'Tester' );

After inserting the records, you can query and verify using below SELECT statement.

Step 3:

Now, we will create a single stored procedure to SELECT, INSERT, UPDATE and DELETE records from the database tables. This operation is also known as the CRUD operation.

SELECT:

In order to retrieve records from the tables, we use SELECT statement

Select * from Employees

INSERT:

 To insert records into the database table, we use the INSERT statement.

Insert into Employees(Id, FirstName, LastName, Age, Gender, Designation) values( @Id, @FirstName, @LastName, @Age, @Gender, @Designation)

UPDATE:

To update specific records into the database table, we use the UPDATE statement.

UPDATE Employees SET  
	FirstName = @FirstName, 
	LastName = @LastName, 
	Age = @Age,  
	Gender = @Gender, 
	Designation = @Designation		
WHERE Id = @Id  

DELETE:

We will the DELETE statement to delete records from the tables.

Delete from Employees Where Id = @Id

Single stored procedure to perform CRUD operations,

Create Procedure [dbo].[EmployeeCurd]
(  
   @Id INTEGER,  
   @FirstName VARCHAR(20) = NULL,  
   @LastName VARCHAR(20) = NULL,  
   @Age int = NULL,  
   @Gender VARCHAR(10) = NULL, 
   @Designation VARCHAR(20) = NULL,
   @ActionType nvarchar(20) = ''  
)  
AS  
BEGIN  
	IF @ActionType = 'Insert'  
	BEGIN  
		Insert into Employees(Id, FirstName, LastName, Age, Gender, Designation) values( @Id, @FirstName, @LastName, @Age, @Gender, @Designation)  
	END  
	IF @ActionType = 'Select'  
	BEGIN  
		Select * from Employees  
	END  
	IF @ActionType = 'Update'  
	BEGIN  
		UPDATE Employees SET  
			FirstName = @FirstName, 
			LastName = @LastName, 
			Age = @Age,  
			Gender = @Gender, 
			Designation = @Designation		
		WHERE Id = @Id  
	END  
	IF @ActionType = 'Delete'  
	BEGIN  
		Delete from Employees Where Id = @Id
	END  
END

Step 4: Insert

We can now execute the stored procedure to insert the records into the database table using the below EXEC query, We need to pass the required parameters along with the 'ActionType' = 'Insert'.

EXEC [dbo].[EmployeeCurd] 
 @Id = 700,
 @FirstName = 'Amelia',
 @LastName = 'John',
 @Age = 35,
 @Gender = 'Female',
 @Designation = 'Business Analyst',
 @ActionType = 'Insert'

You can see the new records is inserted into the table below,

Step 5: Update

To update specific records, we need to pass the required parameters and execute the stored procedure as shown below, we need to pass 'ActionType' = 'Update'.

EXEC [dbo].[EmployeeCurd] 
 @Id = 200,
 @FirstName = 'Rose',
 @LastName = 'Marry',
 @Age = 32,
 @Gender = 'Female',
 @Designation = 'Business Analyst',
 @ActionType = 'Update'

Now, you will see the updated records in the table as shown below,

Step 6: Delete

To delete records from the table, we will use the below query and pass the 'ActionType' = 'Delete',

EXEC [dbo].[EmployeeCurd] 
 @Id = 400,
 @ActionType = 'Delete'

You can see the updated records below,

Step 7: Select

We will use the below EXEC query to see or retrieve the records from the Employees table.

EXEC [dbo].[EmployeeCurd] 
 @ActionType = 'Select'