Well . . . it took a while but the light did come on. I change the definition of @SQL from varchar to nvarchar and it seems to work. Now let's talk about

FOR XML PATH('')), 1,2,'')

vs

FOR XML PATH(''),type).value('.', 'varchar(30)'), 1,2,'')

I think I have that syntax right in the second case. With the first line it runs OK. With the second line it errors with this.

Msg 105, Level 15, State 1, Line 20
Unclosed quotation mark after the character string 'L)) pvt'.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'L)) pvt'.

The letter 'L' mentioned in the error comes from a FundType of 'Local'. (No quotes) Can you suggest what may be going on here?

In a previous click you asked what I meant by the 3rd WHERE. That referred to your suggestion:

set @columns = stuff((
select  ', ' + quotename(FundType) 
FROM ( 
SELECT FundType from TIP_AmendmentProposedFunding P 
WHERE AmendementNumber =  @AmendmentNumber 
UNION
SELECT FundType FROM TIP_AmendmentApprovedFunding WHERE AmendementNumber =  @AmendmentNumber 
) S
WHERE AmendmentNumber = @AmendmentNumber
ORDER BY FundType 
FOR XML PATH('')), 1,2,'')  -- type and value were used in case the FundType contained some characters that may choke XML

In this code block there are 3 WHERE clauses. I think the 3rd is redundant. Don't you agree?