Following on what Mr. Brian Gale wrote about a DELETE causing a massive gap in the IDENTITY property of a table.  It happens sometimes because things which aren't good sometimes...

Based on the code I would guess your ExceptionLog table is empty because the error handling issues the ROLLBACK before the error values are accessed.  But it's not really guaranteed...

It's a great answer from Jeff which exactly matches the output.  My approach also uses Jeff's "number generator" function dbo.fnTally.  While Jeff "didn't use any of your dates" (because it's...

Maybe like this

if @ItemGroup is null
    Select Itemcode,Itemname,ItemGroup
    from tbl1
    where itemgroup is null;
else
    Select...

Maybe extract the constant and put the correlated subquery in the FROM clause as a CROSS APPLY

declare @vmax_var         ...

Maybe a good way could be an UPDATE statement

update t
set flag = case when v.row_count=1 then 'ANY_EMPTY'
          ...

In the situation where Sum([QTY]) returns the correct the number, AVG(OH) or MAX(OH) ought to return only 1 SKU

At a minimum it's missing a comma at the end of the first line.  Is it necessary to nest SELECT statements within the SELECT list as you've done?  It seems...

Yup, I was one of the 'most people' crowd, thanks for alerting me to that, Jeff. Always good to be aware of these extra possibilities. It was also only...

The DIY way could be to...

Concat_ws was originally in MySQL then the other db's borrowed it iirc.  The big news for me is .NET 6 which I'm really liking a lot because it nicely untangles...

CONCAT_WS() "concat with separator" ignores the nulls

select concat_ws(' | ', t.Col1, t.Col2, t.Col3) [output]
from #t t;

The date could be converted without using string functions.  Also, the WHERE clause might be a more appropriate place to filter the rows.  Maybe something this

declare @UPSBatchInvNot...

Why would you want to avoid JOIN?  Maybe use IN but there's no benefit afaik and it might actually be slower

select partc
from #partsc
where VerificationHash in(select VerificationHash
 ...

What is the primary key of the target table?  With Scenario4 you're inserting a new row which would duplicate (or more?) the TaskId.  Once the TaskId is non-unique then further...