The hard task, that's not trivial, is changing to use a sophisticated, automated scripting mechanism to push the same script out to hundreds of databases, or a selected sub-set of...

But the OP doesn't have all that in place, and, as I said before,  it's NOT trivial to do.

Ssometimes the most practical solution is the best. By way...

I'd put the stored procs in the master db.  You have no choice to get a "shared" proc.  The name must start with "sp_".

I'd put the functions in...

Here's my best guess of what you want to do:

UPDATE RXS
SET QTY=RXS_Totals.Qty_Total
FROM FWDB.RX.RXS RXS
INNER JOIN (
  SELECT FacID, PatID, NDC, SUM(Qty)...

Another alternative; you could list up to 3 distinct grp values this way -- beyond 3 you would need to use a different method.

SELECT ABS(id)...

Or change OrdNo to int and avoid all that ridiculous mess!

I prefer to avoid language dependency when possible, and therefore use a "standard" relative day# for day of week rather than a day name.

SELECT CreateDayAdjusted, COUNT(*)

Here's one way:

SELECT CAST(DATEADD(HOUR, 11, OrderDate) AS date) AS Date,
  COUNT(*) AS Count
FROM (VALUES
 (908, '1/3/2018 13:20:34'),(
909, '1/3/2018 15:18:41'),(
910, '1/4/2018 09:15:21'),(

SELECT {column_list, ...}
INTO #TEMP2 -- Max ZATS
from
(
Select
ZBF.[HTS_NUMBER]
,#TEMP1.Part_Num
,row_number() OVER (PARTITION BY Composite_part ORDER BY Insert_Date DESC) AS Max_Date
,Max ([TSI].[ZATS_BROKER_FEED].Insert_Date) as...

Create views to do the joins.  Yes, users shouldn't be expected to correctly write 5-column joins.  And they never should -- they should use a view that does the join(s)...

    , cast (convert(varchar(8), po.PolicyIssueDt, 112) as int) as PolicyIssueDt

    , cast(convert(varchar(8),po.PolicyEfftvDt, 112) as int) as PolicyEfftvDt

    , cast(convert(varchar(8), po.PolicyExpirDt, 112) as int) as PolicyExpirDt

You may or may not need to create another index for StoreNumber first.  It's possible SQL would never use such an index even if you created it.

If you...