I'm trying to make a multi-statement table valued function and failing due to what I think is a limitation of functions and CTEs.
The intended function defines an input parameter(@Param) and and output table(@ResultTable).
It then executes a complex insert statement involving a CTE into that table variable which is (of necessity) terminated by a semicolon.
I then attempt another complex update statement to that table variable involving a CTE and receive an error saying that "Must declare the scalar variable "@ResultTable". Apparently it has dropped out of scope somehow.
I have used this sort of pattern in the past, so my only thought is that the CTEs seem to limit the scope in some way. Is this a known limitation?