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.
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.