Crud Operations In SQL Using Storedprocedure

In this article i explain you how to write crud operations in sql using stored procedure we have an senario
to write all operations write in single stored procedure
Example:-
Step1:- Create Table
Create Table Employee
(
Id                  int primary key identity,
Name            varchar(50),
Designation    varchar(50),
Gender         varchar(50),
Salary           int
)
Step2:-Insert some data into table
Insert Into Employee values('Vasu','Developer','Male',25000)
Insert Into Employee values('kiran','Developer','Male',30000)
Insert Into Employee values('madhu','Tester','Female',40000)
Insert Into Employee values('manasa','Tester','Female',50000)


After write select query and then see the table look like this

Step3:- Create Stored Procedure
Create Procedure usp_Employee
@Id                    int=NULL,
@Action             varchar(50)=NULL,
@Name              varchar(50)=NULL,
@Designation      varchar(50)=NULL,
@Gender           varchar(50)=NULL,
@Salary             int=NULL
AS
Begin
if(@Action='INSERT')
Begin
Insert Into Employee(Name,Designation,Gender,Salary)
values(@Name,@Designation,@Gender,@Salary)
End
Else If(@Action='DELETE')
Begin
Delete from Employee where Id=@Id
End
Else If(@Action='UPDATE')
Begin
Update Employee set Name=@Name,Designation=@Designation,Gender=@Gender,Salary=@Salary where Id=@Id
End
End

If you observe i declared parameters with null ,why because in delete query we need only id and remaining
parameters are not needed that's why i declared parameters with null

Step4:- Write Insert Query, Below is the Insert Query
Exec usp_Employee @Action='INSERT',@Name='siva',@Gender='Male',@Designation='Developer',@Salary=30000

Above query i did not pass id value because id column is the identity column

Step5:- Write Update Query
Exec usp_Employee @Action='UPDATE',@Name='siva',@Gender='Male',@Designation='Developer',@Salary=50000,@Id=5
Step6:-Write Delete Query
Exec usp_Employee @Action='DELETE',@Id=5