Ok, there is a lot to unpack in these procedures, so I'll do my best to lay it out in a comprehendible way.
The way your queries are written, they're likely to contain many non-SARGABLE predications that will produce table scans, and hold long locks. Making them susceptible to blocking and deadlocking each other. You want to make these transaction as short as possible, so they get out of the way of any other incoming transactions. Preferably, there would be a key to link the two tables together, instead of multiple OR conditions. However, if you must use multiple OR conditions, consider this...
Your procedures are accepting a table type parameters. These are similar to table variables, in that they often produce poor estimates and perform poorly. Consider dumping the contents to a #Temp table at the start of the procedure and then joining that to the other tables.
For predicates like this (exist.StringValue IS NOT NULL AND exist.StringValue = inp.StringValue ), instead use exist.StringValue = inp.StringValue. If either value is NULL, it will not return True. No need to explicitly check if the column IS NOT NULL before comparing it to another column.
Eliminate your use of the OR operator. You can use several smaller queries and union them together instead, or several smaller updates back to back for each condition. Using the OR operator as you are, SQL Server is not going to use your indexes, and that will make your transaction stay open longer. I talk about the use of OR in UPDATES https://brendan-mccaffrey.com/2021/12/20/stop-using-the-or-operator/ on my blog. Better yet, is there reason to believe that all columns should not contain data from the source?
Decide if you're more likely to have INSERTS or UPDATES, and follow https://sqlperformance.com/2020/09/locking/upsert-anti-pattern article from https://dba.stackexchange.com/users/1186/aaron-bertrand on the best way to handle UPSERTS, choosing the pattern that most matches your need.
In Summary, for each OR condition that would be an INSERT and/or UPDATE, considering turning it into one simple UPSERT statement. Focus on small and short transactions, and then move on to the next OR condition, and so on. This should allow them to move fast, and avoid blocking and deadlocking.
Edit
Below is an example. I would consider writing one of these statements for each matching pattern you're looking for, and seeing how it performs. In this way, you're breaking each OR condition into its own transaction that should run fast, with minimal blocking. But again, if you had a Key column you could map to between the incoming and existing tables, this would be even easier.
UPSERT
BEGIN TRAN
INSERT INTO FTS.Word (
StringValue, DateValue, TimeValue, NumericValue, Unit,
Metaphone, FK_BaseWord, BaseWordConfidence, IsStopWord
)
SELECT inp.StringValue, inp.DateValue, inp.TimeValue, inp.NumericValue, inp.Unit, inp.Metaphone,
NULL,NULL,
CASE WHEN EXISTS (
SELECT 1
FROM FTS.StopWords
WHERE Word = inp.StringValue
)
THEN 1
ELSE 0
END
FROM @words AS inp
WHERE inp.BaseWord IS NULL
AND NOT EXISTS
(
SELECT 1 FROM dbo.Word WITH (UPDLOCK, SERIALIZABLE)
WHERE Word.Unit = inp.Unit
AND Word.StringValue = inp.StringValue
)
UNION
SELECT inp.StringValue, inp.DateValue, inp.TimeValue, inp.NumericValue, inp.Unit, inp.Metaphone,
base.ID, inp.BaseWordConfidence,
CASE WHEN EXISTS (
SELECT 1
FROM FTS.StopWords
WHERE Word = inp.StringValue
)
FROM @words inp
JOIN FTS.Word base
ON inp.BaseWord = base.StringValue -- only things w/ a string value have base words
WHERE inp.BaseWord IS NULL
AND NOT EXISTS
(
SELECT 1 FROM dbo.Word WITH (UPDLOCK, SERIALIZABLE)
WHERE Word.Unit = inp.Unit
AND Word.StringValue = inp.StringValue
)
UPDATE upd
SET upd.BaseWordConfidence = inp.BaseWordConfidence
FROM FTS.Word upd
INNER JOIN @words AS inp
ON upd.StringValue = inp.StringValue
AND upd.Unit = inp.Unit
WHERE upd.BaseWordConfidence < inp.BaseWordConfidence
COMMIT TRAN
