insert,update delete in one common storeprocedure

Question

i want insert,update 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],Details,TaskOwner,AssignTo,[Status],[Priority],StartDate,DueDate,ReminderDate,isReminder,IsActive,CreatedOn)values (@Id,@Subject,@Details,@TaskOwner,@AssignTo,@Status,@Priority,@StartDate,@DueDate,@ReminderDate,@isReminder,@IsActive,getdate()) 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_ID,SCM_ID,HDM_ID,IDM_NAME,IDM_SNAME,IDM_TIMEPERIOD,IDM_DATE,IDM_STATUS,USER_ID) VALUES (@IDM_ID ,@SCM_ID ,@HDM_ID ,@IDM_NAME ,@IDM_SNAME ,@IDM_TIMEPERIOD,getdate() ,@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

Answers (2)