Hi Team, I am having some cursor (RestrictionOfficeDetailsCursor ) in a sql stor proc. For dead lock issue it seems. very inefficient loop and can be simplified with a few update statements and an insert statement. Can you please suggest if we can use update and insert statement instead of Cursor triggering.
code snippets as below.
DECLARE RestrictionOfficeDetailsCursor CURSOR
FOR SELECT [Id],[EntityId],[OfficeId]
FROM @RestrictionOfficeDetails
--WHERE [RestrictionId]=@tmpRestrictionId
ORDER BY [Id],[EntityId],[RestrictionId],[OfficeId]
OPEN RestrictionOfficeDetailsCursor
FETCH NEXT FROM RestrictionOfficeDetailsCursor INTO @RestrictionOfficeId,@EntityId,@OfficeId
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @RestrictionOfficeId=ISNULL(@RestrictionOfficeId,0)
SELECT @EntityId=ISNULL(@EntityId,0)
SELECT @OfficeId=ISNULL(@OfficeId,0)
IF @RestrictionOfficeId>0
BEGIN
SELECT @OldOfficeId=[OfficeId] FROM dbo.[RestrictionOffice] (NOLOCK) WHERE [Id]=@RestrictionOfficeId
IF @OldOfficeId<>@OfficeId
BEGIN
UPDATE dbo.[RestrictionOffice] SET [OfficeId]=@OfficeId,
[ModifiedBy] = @auditId,
[DateModified]=@CurrentDateTime
WHERE [Id]=@RestrictionOfficeId
---- Handle Error
SELECT @Error=@@Error
IF (@Error <> 0 OR @ExitCode <> 0)
BEGIN
SET @ErrorMsg = 'Error occurred while updating RestrictionOffice'
GOTO errHandler
END
END
END
ELSE
BEGIN
IF EXISTS(SELECT [Id] FROM dbo.[RestrictionOffice] (NOLOCK) WHERE [RestrictionId]=@tmpRestrictionId AND [OfficeId]=@OfficeId)
BEGIN
IF NOT EXISTS(SELECT [Id] FROM dbo.[RestrictionOffice] (NOLOCK) WHERE [RestrictionId]=@tmpRestrictionId AND [OfficeId]=@OfficeId AND [Status]='A')
--BEGIN
--SELECT @OfficeName = [Description] FROM dbo.[Office] (NOLOCK) WHERE Id = @OfficeId --AND @Status = 'A'
--SET @ErrorMsg = 'Restriction already have ' + @OfficeName + ' as Office.'
--GOTO errHandler
--END
--ELSE
BEGIN
UPDATE dbo.[RestrictionOffice] SET [Status]='A' ,
[ModifiedBy] = @auditId,
[DateModified]=@CurrentDateTime
WHERE [EntityId]=@EntityId
AND [RestrictionId]=@tmpRestrictionId
AND [OfficeId]=@OfficeId
--AND [Status]='I'
---- Handle Error
SELECT @Error=@@Error
IF (@Error <> 0 OR @ExitCode <> 0)
BEGIN
SET @ErrorMsg = 'Error occurred while updating RestrictionOffice'
GOTO errHandler
END
END
END
ELSE
BEGIN
INSERT INTO RestrictionOffice([EntityId],[RestrictionId],[OfficeId] ,[Status],[CreatedBy],[ModifiedBy])
VALUES(@EntityId,@tmpRestrictionId,@OfficeId,'A',@auditId,@auditId)
---- Handle Error
SELECT @Error=@@Error
IF (@Error <> 0 OR @ExitCode <> 0)
BEGIN
SET @ErrorMsg = 'Error occurred while inserting into RestrictionOffice'
GOTO errHandler
END
END
END
FETCH NEXT FROM RestrictionOfficeDetailsCursor INTO @RestrictionOfficeId,@EntityId,@OfficeId
END
BEGIN TRY
CLOSE RestrictionOfficeDetailsCursor
DEALLOCATE RestrictionOfficeDetailsCursor
END TRY