There are many impediments to performance in your query. You want it to run as quickly as possible. Test this as a functional equivalent.

;

WITH TheChosenOne AS (

SELECT TOP 1

t1.TesterID_AA,

t1.Tester_AA,

t1.AutoAssignedDate,

t1.ManagerAutoAssign,

t1.[SR Number]

FROM SWProjectGrid.dbo.tblServiceRequests AS t1

/* CJM GET RID OF THIS FUNCTION! It will kill performance! */

INNER JOIN SWProjectGrid.dbo.tbl_AutoAssign_TesterCategoryAssignments AS A

ON dbo.func_AutoAssign_CategoryCheck(t1.[SR Number]) = A.CatID

/* CJM this join is redundant. It doesn't filter any rows */

--LEFT JOIN SWProjectGrid.dbo.tblTestOpsValues AS V

-- ON V.ID = dbo.func_AutoAssign_CategoryCheck(t1.[SR Number])

INNER JOIN SWProjectGrid.dbo.Medicare_Renewal_Copays t2

ON t2.SR = t1.[SR Number]

/* CJM Redundant - repeated in WHERE clause */

--AND t2.[Copay Grid Status] = 'Copay Automation Complete'

--AND t2.[Accumulator/CBM Status] = 'Setup Complete'

--AND ISNULL(t2.AutoAssigned, 0) = 0

WHERE t1.[Sub Area] NOT IN ('Question','DIV Implementation')

AND t1.[Closed Date] IS NULL/* Not Closed */

AND t1.STATUS = 'Set-up Complete'/* Status is Set-up Complete */

AND ISNULL(t1.TesterID_AA, '') = '' /* A tester has not been assigned */

AND A.UserID = @LanID/* The user that clicked GetWork */

AND

--(

/* If 137 then it must be Automation Complete and Setup Complete other than 137 not necessary */

/* CJM don't these cancel out and become a NULL filter, which in turn is rendered */

/* redundant by the equal to operator in the join between t1 and A? */

--dbo.func_AutoAssign_CategoryCheck(t1.[SR Number]) <> 137

--OR dbo.func_AutoAssign_CategoryCheck(t1.[SR Number]) = 137

-- these turn the LEFT JOIN t2 into an INNER JOIN

t2.[Copay Grid Status] = 'Copay Automation Complete'

AND t2.[Accumulator/CBM Status] = 'Setup Complete'

AND ISNULL(t2.AutoAssigned, 0) = 0

--)

ORDER BY t1.[SR Number]

)

UPDATE TheChosenOne SET

TesterID_AA = @LanID,

Tester_AA = dbo.Func_BT_BatchQueueGetName(@LanID),

AutoAssignedDate = GETDATE(),

ManagerAutoAssign = 0

/* CJM why not insert directly into the permanent table? */

OUTPUT inserted.ID

,inserted.[SR Number]

,inserted.AccountName

,inserted.Area

,inserted.[Sub Area]

,inserted.Carrier

,inserted.Div

INTO @tblSRInserts