Insert Update and Delete in One Stored Procedure

Raj.Trivedi
Posted by Raj.Trivedi under Sql Server category on | Points: 40 | Views : 1556
Hello Team,

This code will help you to do Insert Update and delete with one Stored Procedure.

The main logic to identify when to Insert new record and Update the existing record and delete the record is through mode.

You have to send the mode from front end hard code.

If you send mode 0 then it will insert the record and if you send the mode 1 it will update the record for the corresponding id and if you send mode 2 then it will delete the record with the corresponding id.

Stored Proc

create Proc [dbo].[INS_UPD_DEL_Categories]
(
@Mode char(1),
@CategoryID uniqueidentifier,
@CategoryName varchar(50),
@CategoryDescription varchar(200),
@Category_Status tinyint,
@LoggedInUser uniqueidentifier
)
as
begin
declare @@reccount int
begin try
if(@Mode='0')
begin
set @@reccount = (select COUNT(CategoryName) from CategoriesMaster where CategoryName = @CategoryName)
if (@@reccount =0)
begin
insert into CategoriesMaster(CategoryID,CategoryName,CategoryDescription,Category_Status,CreationDate,CreatedBy)
values (@CategoryID,@CategoryName,@CategoryDescription,@Category_Status,getdate(),@LoggedInUser)
end
end
else if(@Mode='1')
begin
update CategoriesMaster set CategoryName=@CategoryName,CategoryDescription=@CategoryDescription,
Category_Status=@Category_Status,ModifiedDate=getdate(),ModifiedBy=@LoggedInUser where CategoryID = @CategoryID
end
else if(@Mode='2')
Update CategoriesMaster
set Category_Status = 0,
RowDeleted = 1,
ModifiedDate=getdate(),
ModifiedBy=@LoggedInUser
where CategoryID = @CategoryID
end try
begin catch
end catch
end

Comments or Responses

Login to post response