I declared a table in my procedure which you can see below:
Declare @resultTable Table
(
EmpId int,
EmpStatusId int,
CreatedDateTime datetime
)
and i perform a delete function:
Delete From ActualTable
Where Id = (Select EmpId from @resultTable with (nolock) where EmpStatusId = @tempId)
i am trying to avoid locking in the select statement to avoid deadlock even if i read a dirty data. However, "with (nolock)" is not allowed. The error says:
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Is there a way to apply NOLOCK in a temporary table?
I'd question whether you're asking the right question:
You own the table variable (note: its not a temp table, but a table variable), so there's no point issuing a nolock against it. You appear to want nolock issued against the target table, ActualTable, but a delete HAS to hold locks. Period.
The usual technique with handling large delete operations is to issue the deletes in batches of row ranges.