Hi,
Need advice with the below stored procedure to avoid concurrency problems.
Create PROCEDURE [SD_SaveUpdate_DepartmentDetails]
@DeptID int,
@DepartmentName varchar(100),
@DepartmentID int out
AS
BEGIN
Begin Try
-------------------------Insert Update Department Details-----------------------
Begin
SET NOCOUNT ON
BEGIN TRAN Department
IF EXISTS(SELECT * FROM DepartmentTable WITH (UPDLOCK, HOLDLOCK) WHERE DepartmentName= @DepartmentName and Isdeleted=0)
BEGIN
UPDATE DepartmentTable SET DepartmentName= @DepartmentName WHERE DeptID = @DeptID
set @DepartmentID=@DeptID
END
ELSE
BEGIN
INSERT INTO DepartmentTable (DepartmentName) VALUES (@DepartmentName)
set @DepartmentID=@@IDentity
END
COMMIT Transaction Department
End
-------------------------Insert Update Department Details---- ...
Go to the complete details ...