Running the below results in an error. Am still trying to understand why this should be the case? Okay, so perhaps it could result in a choice of sequence values resulting in an indeterminant result and that is why but even SQL Server 2017 has these nonsensical limitations when using sequences. Oracle wins hands down on this.
Msg 11741, Level 15, State 1, Line 33
NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.
begin transaction
DECLARE @tracked_Company_return tracker.Common_Company;
INSERT INTO Common.Company (
CompanyIdentifier
,RecordNo)
SELECT
CompanyIdentifier
,CASE WHEN 1 = 1 then (NEXT VALUE FOR [domain].[DomainRecord])
ELSE 2
end
RecordNo
FROM
@tracked_Company_return
rollback transaction