Monday, 26 September 2016

Insert, update, delete, select record using stored procedure in SQL-Server

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 SQL 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
Share:

0 comments:

Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON