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:

In the previous article I have explained Sql commands and its types, Format the emails using StringBulider in asp.net, Explain cookies in asp.net OR Explain cookies with example in asp.net and Send email content as attachment in PDF format in asp.net. 

I have created a table name tb_Emp:

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.