insert,update delete in one common storeprocedure
i want insert,update delete in one common storeprocedure it is possible if yes how can i do this insert update delete??
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Amit ShrivastavaPosted Sep 12, 2014, 2:29 AM
/****** 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 PoddarPosted Sep 12, 2014, 2:21 AM
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