Why are we able to not perform saved procedure in the function once the opposite can be done?

I suspect the reason being the execution of the function isn't designed to modify data by any means, and permitting you to definitely operate a saved procedure would allow you to do that...

You should improve your saved procedure to some Function to refer to it as from inside a Function.

Or, one of the ways is by using xp_cmdshell to call a load file in which the batch file consists of the execute procedure statement. Within the function you are able to call the extended proc.

eg.

Create Function...

EXEC master.sys.xp_cmdshell 'C:\test.bat'

RETURN...

I'm by no means stating that this really is sound practice but am just saying it is a possibility.

Technically, calling a saved procedure from the function can be done. But don't forget the objective of the saved procedure and processes.

Reason for function: The function can be used to compute something and therefore must return something. A function could be known as from the choose statement as lengthy as it doesn't alter data. (Permanent table data, not temp tables)

Reason for Saved procedure: The saved procedure can be used to complete business logic and therefore might return something.