Refactoring a procedure heavy database and the boundaries confuse me: calling functions inside procedures seems fine, calling procedures inside functions errors and the reasons matter for how I split logic.
What are the actual rules and the sanctioned workarounds?
The asymmetry is real and principled: functions promise the engine they change nothing, procedures promise nothing at all and every rule follows from that one distinction.
The permitted direction, functions inside procedures, is unrestricted: scalar functions in any expression, SELECT dbo.CalcTax(@amount), table valued functions in FROM clauses joined like tables, all ordinary. The performance asterisk belongs here rather than any legality one: scalar functions invoked per row across large sets execute row by row with real cost, and multi statement table valued functions hide their cardinality from the optimizer, so the refactoring instinct to wrap everything in neat scalar functions deserves restraint on hot paths, inline table valued functions being the performing shape when a function must sit in heavy queries.
The forbidden direction, procedures inside functions, fails because a function may run anywhere an expression runs, mid SELECT across a million rows and a procedure may do anything at all, write tables, change settings, so the engine forbids the combination outright along with the rest of the side effect family inside functions, no INSERT into real tables, no dynamic SQL, no temp table creation. Hitting this wall during refactoring is a design signal rather than an obstacle: logic that needs side effects is procedure logic, logic that computes answers is function logic and the piece that wants to be both wants splitting.
The sanctioned patterns where the wall stands: a procedure's result set lands in a table for further work through INSERT INTO #results EXEC dbo.TheProc, the capture pattern, procedure calling procedure with data handed through temp tables visible down the call chain and computation shared between a function and a procedure extracted so the function computes while the procedure computes then acts. What does not exist is a sanctioned procedure call inside a function, the OPENQUERY loopback tricks circulating in old forum posts deadlock and distort under load, clever exactly once.
The design signal framing reorganized the whole refactor: three things fighting the wall turned out to be compute then act pairs wanting splitting exactly as described. The scalar function restraint note also explained a slow report we had blamed on indexes. Two lessons, one thread.