Why can we not execute a stored procedure inside a function in SQL Server

Why can we not execute a开发者_如何学JAVA stored procedure inside a function when the opposite is possible?

You cannot execute a stored procedure inside a function, because a function is not allowed to modify database state, and stored procedures are allowed to modify database state.

This is by definition (see CREATE FUNCTION - Limitations and Restrictions).

User-defined functions cannot be used to perform actions that modify the database state.

A stored procedure might modify database state, or it might not. But the SQL Server compiler shouldn't have to analyze the stored procedure to know whether or not it modifies database state. Therefore, it is not allowed to execute a stored procedure from within a function.

Functions exist to simply calculate something, a value or a table result, nothing more than that. These functions can be called within a SELECT query for instance, e.g.

SELECT calculate_something(a) FROM some_table;

Now consider what would happen if the function calculate_something were allowed to execute a stored procedure which would delete all rows in some_table. Your intention is to calculate something using the value of the some_table.a columns, but you end up... deleting all rows in some_table. That is clearly not something you want to happen.

I know this is already been answered but in SQL server the function is not suppose to change the data but the procedure is meant to.

In addition to this i like to add that we cannot select a procedure or put it in a where clause but we can do this with a function.

We use function to shorten the code so its greatly helpful as it reduces a lot of query for the coder.

Hope this helps.

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

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.

We cannot call store procedure within a function. However, we can call a function within a store procedure.

Functions are extremely limited. They cannot perform any operation in any way that can change data. This means that you can't use dynamic sql or call other objects (except functions)

Some restrictions are their for functions,like (i) it should not change any table structure .It should readonly table. But Stored Procedure can change. Stored procedure can do any changes. So we cant call a Stored Procedure from function.

We can call procedure inside function but that function cannot be called through select statement.The function works fine if you call it through another calling program.Same is the case with dml operations.Functions can have dml operations but it cannot be called through select statement.Whereas if you call the function through another program, the dml gets executed

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.