Wednesday, November 19, 2014

Get, insert, update and delete the data using one stored procedure in Sql server

Introduction: Today I am going to explain how we can Get, Insert, Update and Delete the records using one store procedure in sql server
Description:
I have created a table name tb_Emp:
stored procedure in Sql server

Now I create a store procedure to perform all action:

Create procedure Emp_Tb_IUDS
(
@command varchar(50),
@id int =null,
@name varchar(50)=null,
@address varchar(50)=null,
@salary int=null,
@phoneN int=null
)
as begin
set nocount on;
if @command ='Insert' --To insert the records
Begin
insert into dbo.tb_Emp (Emp_Name,Emp_Address,Emp_Salary,Emp_PhoneNo) values(@name,@address,@salary,@phoneN)
end
else if @command='Update'  --To Update the records
Begin
Update dbo.tb_Emp set Emp_Name=@name,Emp_Address=@address,Emp_Salary=@salary,Emp_PhoneNo=@phoneN where
 Id= @id
 End
 Else if @command = 'Delete'  --To Delete the records
 Begin
 Delete from dbo.tb_Emp where Id = @id
 end
 Else if @command = 'Select'  --To Select the records
 Begin
 Select * from dbo.tb_Emp
 End
 End

To check the store procedure is working properly or not, execute it.

To insert the records:
Exec Emp_Tb_IUD @command='Insert',@name='Ramesh',@address='Himachal pradesh',@salary=10000,@phoneN=78456123

To update the records:
Exec Emp_Tb_IUD @command='Update',@id='1',@name='Tarun',@address='Delhi',@salary=24000,@phoneN=741258635

To delete the records:
Exec Emp_Tb_IUD @command='Delete',@id='2'

To select the records:
Exec Emp_Tb_IUD @command='Select'


Is this article helpful for you?

If yes post your comment to appreciate my work and fell free to contact me. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email.

1 comment: