insertupdate delete in one common storeprocedure

Question

i want insertupdate delete in one common storeprocedure it is possible if yes how can i do this insert update delete??

Amit Shrivastava · Answer

thanks for your reply but please see my example /****** Object: StoredProcedure [dbo].[usp_SaveTask] Script Date: 09/12/2014 11:42:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO if(@cnt=0)Record insert if(@cnt!=0)Record Update if(@cnt!=??)Record delete???? Create PROCEDURE [dbo].[usp_SaveTask] @Id bigint @Subject nvarchar(128) @Details nvarchar(max) @TaskOwner nvarchar(56) @AssignTo nvarchar(56) @Status nvarchar(56) @Priority nvarchar(56) @StartDate datetime @DueDate datetime @ReminderDate datetime =null @isReminder bit @IsActive bit AS BEGIN Declare @Cnt Int; Set @Cnt =0; Select @Cnt =Count(Id) From tblTask where Id=@Id If(@Cnt=0) Begin select @Id=isnull(max(Id+1)1) from tblTask insert into tblTask(Id[Subject]DetailsTaskOwnerAssignTo[Status][Priority]StartDateDueDateReminderDateisReminderIsActiveCreatedOn)values (@Id@Subject@Details@TaskOwner@AssignTo@Status@Priority@StartDate@DueDate@ReminderDate@isReminder@IsActivegetdate()) End Else begin update tblTask set ReminderDate=@ReminderDate isReminder=@isReminder [Priority]= @Priority  ModifiedOn=getdate() where Id=@Id End else if(@cnt=??) begin delete tblTask where where Id=@Id end END

Niranjan Poddar · Answer

check this procedure CREATE PROCEDURE [dbo].[PROC_IND_MAST] (@IDM_ID int@SCM_ID int@HDM_ID int@IDM_NAME varchar(60) @IDM_SNAME varchar(20)@IDM_TIMEPERIOD varchar(50) @IDM_STATUS int@USER_ID int@TRANSTYPE VARCHAR(2)) AS BEGIN IF(@TRANSTYPE='I') BEGIN SELECT @IDM_ID=ISNULL(MAX(IDM_ID)0)+1 FROM IND_MAST; INSERT INTO IND_MAST (IDM_IDSCM_IDHDM_IDIDM_NAMEIDM_SNAMEIDM_TIMEPERIODIDM_DATEIDM_STATUSUSER_ID) VALUES (@IDM_ID @SCM_ID @HDM_ID @IDM_NAME @IDM_SNAME @IDM_TIMEPERIODgetdate() @IDM_STATUS@USER_ID) END ELSE IF(@TRANSTYPE='U') BEGIN UPDATE IND_MAST SET SCM_ID = @SCM_ID HDM_ID = @HDM_ID IDM_NAME = @IDM_NAME IDM_SNAME = @IDM_SNAME IDM_TIMEPERIOD = @IDM_TIMEPERIOD IDM_DATE = getdate() IDM_STATUS = @IDM_STATUS USER_ID = @USER_ID WHERE IDM_ID = @IDM_ID END ELSE IF(@TRANSTYPE='D') BEGIN DELETE FROM IND_MAST WHERE IDM_ID = @IDM_ID END END

insert,update delete in one common storeprocedure