When you create a weekly job schedule in SQL, you specify "Wednesday", as in every Wednesday (or specified identified Weds of the month), but you do not list all future...

No.  SQL Server itself does not store that type of info.  It would be too much overhead on the system.

If the table has a unique key, you could...

I strongly prefer to avoid code that requires a specific DATEFIRST setting; my code works the same under any setting.
But this:
WHERE DatePart(dw, d)=2
adds a dependency on the...

SELECT
    passed_date, DATEADD(DAY, -DATEDIFF(DAY, 0, day7) % 7, day7) AS calcd_date
FROM (
    VALUES(CAST('20180522' AS date)),('20180603'),('20180704')
) AS test_data(passed_date)
CROSS APPLY (
 ...

Why the need to over-engineer everything?  Each entry is, for example, "Wednesdays" not a specific Wednesday,  Simple enough.  Although I do agree that "Monday", "Monday" in the same row should not...

You're welcome, let me know how it goes.

Btw, it's inconsistent to use data type "datetime2" and GETDATE(), which returns only a "datetime" value.  That's why I used SYSDATETIME()...

UPDATE pr
SET fieldvalue = pe.requestind,
    datelstmod = SYSDATETIME()
FROM process pr
INNER JOIN person pe ON pe.personid = pr.personid AND pr.activityid = 991
WHERE...

This a common technique ("trick") to get a single line of values from multiple lines of input.  It's almost always at least a pair of conditions, e.g.:
MAX(CASE WHEN rline...

To the OP:

I'd suggest not over-complicating what you have unless/until it's actually needed.  Why spends hours doing something that may never be applicable or useful?  If the conditions change,...

This should be enough to UPDATE the udf table:

UPDATE udf
SET Value = CASE udf.fieldno WHEN 1 THEN udf1 WHEN 2 THEN udf2 WHEN 3...

As noted, use NOT EXISTS.  Also, uniquely cluster the NoEmail table on the EmailAddress.

...
CREATE TABLE #tblNoEmail
(
EmailAddress varchar(100) PRIMARY KEY CLUSTERED
)
...
SELECT...

2) The question should be. When needing the result from one query multiple times in a procedure/UDF/trigger, is it best to store it in a variable or run that...

Local variables tend to slow down scalar functions, so avoid them if possible.  In this case, they also make the logic much more complicated than it needs to be. ...