In this article, I will explain you how to perform select, insert, update and delete operations on a table using sql-server stored procedure. Below is the step by step tutorial.

Step 1: Creating a table

First, we need to create a table. Execute below script to create a new table.

create table tblStudent
(
Id int primary key identity,
Name varchar(20),
Gender varchar(10),
TotalMarks int 
)

Step 2: Creating a stored procedure

Now, create a stored procedure usp_Student which will take 5 parameters as input parameters. Below is the script to create procedure.

create procedure usp_Student
@Id int=null, 
@Name varchar(20)=null,  
@Gender varchar(10)=null,  
@TotalMarks int=null,
@OperationType varchar(10)=null 
as
begin
 If(@OperationType='Insert')
begin
insert into tblStudent(Name,Gender,TotalMarks) values(@Name,@Gender,@TotalMarks)  
end
 If(@OperationType='Select')
begin
select * from tblStudent
end   
 If(@OperationType='Update')
begin
update tblStudent set TotalMarks=@TotalMarks where Id=@Id
end
 If(@OperationType='Delete')
begin
delete from tblStudent where Id=@Id
end    
end

Step 3: Inserting records

Now, we will execute the stored procedure to insert the records. Use @OperationType='Insert' to insert the records.

exec usp_Student @Name='Rahul',@Gender='Male',@TotalMarks=95,@OperationType='Insert'
exec usp_Student @Name='John',@Gender='Male',@TotalMarks=90,@OperationType='Insert'
exec usp_Student @Name='Mary',@Gender='Female',@TotalMarks=85,@OperationType='Insert'
exec usp_Student @Name='Mathew',@Gender='Male',@TotalMarks=90,@OperationType='Insert'

Step 4: Selecting all records

Now, execute the procedure to select all the records. Use @OperationType='Select' to insert the records.

exec usp_Student @OperationType='Select'

Below is the output.

Id Name Gender TotalMarks
1 Rahul Male 95
2 John Male 90
3 Mary Female 85
4 Mathew Male 90

Step 5: Updating a record

Now, update the Rahul's Total Marks from 95 to 99 using Id parameter. Use @OperationType='Update' to update the record. Below is the script to update record.

exec usp_Student @Id=1,@TotalMarks=99,@OperationType='Update'

Now, execute the procedure to see updated record.

exec usp_Student @OperationType='Select'

Below is the output.

Id Name Gender TotalMarks
1 Rahul Male 99
2 John Male 90
3 Mary Female 85
4 Mathew Male 90

Step 5: Deleting a record

Now, we will delete Mathew's record by using Id parameter. Use @OperationType='Delete' to delete the record. Below is the script to delete record.

exec usp_Student @Id=4,@OperationType='Delete'

Now, execute the procedure to see all records after deleting one record.

exec usp_Student @OperationType='Select'

Below is the output.

Id Name Gender TotalMarks
1 Rahul Male 99
2 John Male 90
3 Mary Female 85