Wow...really I never thought about it. Okay so actions speak louder than words so could you please optimize everything except for the "Alter Proc".
Table variables are slow (and you can probably manage with only one or even none).
Cursors are slow
Your string split is probably slow (can't tell, it wasn't included)
The variable assignments can be done in a single statement.
Can't tell how the final insert can be optimised. The multiple DISTINCTS are probably unnecessary though.
I can definitely give it a shot but I am sure I won't end up landing anywhere.
So give it a shot. You'll learn a lot more by trying and getting suggestions than by someone doing it for you.
If you want your code (not just this) optimising by someone else, suggest to your boss that you get a consultant in to look at it.
One thing would be the table variables, could be replaced by CTEs.
😎
Wealth of antipatterns in this code; multiple table variables, cursor using table valued function, multi-valued input parameter, distinct selection from a table valued function etc.