Realistically at this point, I'm just trying to gather a better understanding of what is going on here for my own sanity.  If the Execution plan shows SQL expects...

Personally, I'd try using a temp table and see if it works well before I spent lots of time trying other work-arounds:

declare @p3 dbo.IdList

select * into #p3 from @p3

create unique...

I don't know Snowflake SQL, but based on Google for their syntax, derived tables are allowed in an UPDATE:

UPDATE target SET v = b.v /* actual UPDATE example from Snowflake...

I think it's easier to use ROW_NUMBER(), like this:

;WITH cte_STUDENTIDPOOL AS (
    SELECT STATESTUDENTID, ROW_NUMBER() OVER (ORDER BY STATESTUDENTID) AS row_num
   ...

Yes, that is correct.

Roughly:

;WITH cte_person AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY hPerson ORDER BY hID) AS row_num,
        ROW_NUMBER() OVER(PARTITION...

Typically that's done with a cte that uses ROW_NUMBER().  Easy for first/specific row num, but for last to be easy, you'd have to do a DESC ORDER BY in the...

I prefer a cross-tab approach for these types of queries:

;WITH cte_data AS (
    SELECT MyId, PayeeId, ROW_NUMBER() OVER(PARTITION BY MyId ORDER BY PayeeId) AS...

Because SQL, deliberately, has delayed verification.  This allows you to create a proc before other objects exist.  For example, this should run fine:

USE tempdb;

GO

CREATE PROC proc1 AS SELECT * FROM...

If you need to update PoolId based on another table, you should do the lookup in the same query as the UPDATE.  And there's no reason to update PoolId twice...

DECLARE  @intID    BIGINT
        ,@bridgeid BIGINT = 1
;
 UPDATE dbo.bridge 
    SET @intID...

Cluster STXL on ( MANDT, TDOBJECT, TDNAME ) instead of creating all those separate non-clus indexes.  If those key columns are not inherently unique, and you can add...

In case you want it, here's a function to prep the string as we discussed:

DECLARE @string nvarchar(max);

SET @string = '"A1","A2","A3","A4","A5"

"000066666XYZ",00002,"","","1,000,000"';

SET @string = REPLACE(@string, CHAR(13) + CHAR(10), ',');

SELECT @string, dbo.prepare_string_for_split(@string, DEFAULT,...

I look at missing index stats quite frequently.  Very often I don't add or update index based on them, but I still review the stats just in case.  Sometimes a...

Cluster STXL on ( MANDT, TDOBJECT, TDNAME ) instead of creating all those separate non-clus indexes.  If those key columns are not inherently unique, and you can add a single...