Thanks all. I have some things to try once I have write access to a development copy of the database.

Is it possible that LGTC_ShipmentsClaims is empty (or at least contains no matching rows) at the time your stored procedure is run?

Here's the next key table.
create table SEQ_SYSTEMKEY
  (LASTKEY int null) on [PRIMARY];

and here's the current stored procedure.
create procedure nextSID
as
 ...

The code in the sproc just does an update of column Last_Key on the one row in that table and returns the new value. That's not a real sequence is...

Thanks, but I don't think I can amend the 3rd party procedure and I'm not sure this is significantly different from updating the table directly with +1 +@NumSids. With either...

I tend to structure stored procedures like this.

begin try
  -- Do some stuff
  return 0;
end try

begin catch
  -- Do some...

The approaches already suggested are the way to go, but you need to consider the criteria you use when defining the subset (I assume you're intending to use the smaller...

The UPDATE will set the DateOfOccurrence to its existing value (ie. have no effect) because the column is also part of the JOIN criteria.