stored procedure with insert & update using identity column
12,188
First of all, give your input variable TaskID a default value like below, then simply check to see if the variable is NULL, if so, insert a new row
Alter PROCEDURE TaskSave
(
@taskid int = NULL,
@ProjectId int,
@EmployeeId int,
@TaskName nvarchar(50),
@Duration_Hrs int,
@StartDate nvarchar(20),
@FinishDate nvarchar(20),
@CreateUserId int,
@CreatedDate nvarchar(20),
@ModifiedUserID int,
@ModifiedDate nvarchar(20),
@Is_CommonTask bit
) AS
BEGIN
IF @taskid IS NULL
BEGIN
INSERT TblTasks
VALUES (@ProjectId,@EmployeeId,@TaskName,@Duration_Hrs,
@StartDate,@FinishDate,@CreateUserId,@CreatedDate,
@ModifiedUserID,@ModifiedDate,@Is_CommonTask)
END
ELSE
BEGIN
UPDATE TblTasks SET
StartDate=@StartDate,FinishDate=@FinishDate,
Duration_Hrs=@Duration_Hrs
WHERE TaskId=@TaskId
END
END
GO
Author by
john
Updated on June 04, 2022Comments
-
john almost 2 years
I have a table called 'tasks' in that
'task id'
is identity column, for that table I have to write save stored procedure, in which when 'task id' is not given it should insert the values and when 'task id' is given it should update the table.how can this achievable when task id is identity column could anyone explain with example. here is the code
Alter PROCEDURE TaskSave ( @taskid int, @ProjectId int, @EmployeeId int, @TaskName nvarchar(50), @Duration_Hrs int, @StartDate nvarchar(20), @FinishDate nvarchar(20), @CreateUserId int, @CreatedDate nvarchar(20), @ModifiedUserID int, @ModifiedDate nvarchar(20), @Is_CommonTask bit ) AS BEGIN IF Exists( select null from TblTasks where TaskId=@TaskId) BEGIN INSERT TblTasks VALUES (@ProjectId,@EmployeeId,@TaskName,@Duration_Hrs, @StartDate,@FinishDate,@CreateUserId,@CreatedDate, @ModifiedUserID,@ModifiedDate,@Is_CommonTask) END ELSE BEGIN UPDATE TblTasks SET StartDate=@StartDate,FinishDate=@FinishDate, Duration_Hrs=@Duration_Hrs WHERE TaskId=@TaskId END END GO