Why can we not execute a stored procedure inside a function in sql server 2005?

Question

Why can we not execute a stored procedure inside a function when the opposite is possible?

Answer 1

I suspect this is because the execution of a function is not supposed to modify data in any way, and allowing you to run a stored procedure would let you do this...

Answer 2

You would need to change your stored procedure to a Function to call it from within a Function.

Or, one way is to use xp_cmdshell to call a batch file where the batch file contains the execute procedure statement. In the function you can call the extended proc.

eg.

Create Function...

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

RETURN...

I am in no way saying that this is good practice but am just saying it's a possibility.

Answer 3

Technically, calling a stored procedure from a function is possible. But remember the purpose of the stored procedure and functions.

Purpose of function: The function is used to compute a value and hence must return a value. A function can be called from a select statement as long as it does not alter data. (Permanent table data, not temp tables)

Purpose of Stored procedure: The stored procedure is used to execute business logic and hence may or may not return a value.