Hi can anyone give me some tips on how to tune the following stored procedure as its giving me deadlocks?
CREATE PROCEDURE Check_CaseLockMessages
@UserId as int,
@Type as tinyint
AS
/*
Created By: Chris Kerrison
Date Created: 30 Apr 2001
Purpose: determines whether a message should be send to a user relating to a case. The Store Proc works in two 2 ways:
1) To identify if any other users are requesting a case opened by the User Id passed in
2) To identify to a user that a Case they have request has been released by another user
*/
if @type = 2
BEGIN
Select csCaseID,
FirstName + ' ' + Surname as UserName
From CSCaseLock a with (nolock), HRHumanResources b
where a.LockType = 2
and a.UserId = b.HRHumanResourceID
and a.LockingUserId = @UserID
and a.FlaggedToUser = 0
order by csCaseID
-- Update these records so that a message doesn't get sent again
Set NoCount On
BEGIN TRANSACTION
Update CSCaseLock
Set FlaggedToUser = 1
Where LockType = 2
and LockingUserId = @UserID
COMMIT TRANSACTION
Set NoCount Off
END
ELSE
BEGIN
Select csCaseID,
FirstName + ' ' + Surname as UserName
From CSCaseLock a with (nolock), HRHumanResources b
where a.UserId = b.HRHumanResourceID
and a.LockType = 3
and a.UserID = @UserId
Order by CSCaseID
END
GO
Cheers
Where LockType = 2
and LockingUserId = @UserID