Thank you all for the time.Without using the Locking concept(SET TRANSACTION ISOLATION LEVEL) or any UPDLOCK, i tried the below SP
Create PROCEDURE [SD_SaveUpdate_DepartmentDetails]
@DeptID int,
@DepartmentName varchar(100),
@DepartmentID int out
AS
BEGIN
Begin Try
Begin
SET NOCOUNT ON
BEGIN TRAN Department
IF Not EXISTS(SELECT * FROM DepartmentTable WHERE DepartmentName= @DepartmentName
and Isdeleted=0 and DeptID <> @DeptID)
and @DeptID > 0
BEGIN
UPDATE DepartmentTable SET DepartmentName= @DepartmentName,lastupdated=getdate() WHERE DeptID = @DeptID
set @DepartmentID = @DeptID
END
ELSE IF NOT EXISTS(SELECT Top 1 DepartmentName FROM DepartmentTable WHERE DepartmentName= @DepartmentName and IsDeleted=0)
and @DeptID=0
BEGIN
INSERT INTO DepartmentTable (DepartmentName,isdeleted,lastupdated) VALUES (@DepartmentName,0,getdate())
set @DepartmentID=Scope_IDentity()
END
Else
Begin
set @DepartmentID = -1
End
COMMIT Transaction Department
End
End Try
Begin Catch
set @DepartmentID = 0
End Catch
End
To confirm if there is any duplicate value,I tested this SP using SQLQUERYSTRESS tool for multiple users scenario (concurrency) and its successful.No duplicate values.
Please let me know in case of any suggestions.