January 10, 2016 at 8:03 am
Hi Friends,
Why does you can't call SP in UDF? Please tell.
January 10, 2016 at 9:28 am
A user-defined function can be called from within a query. And a query is optimized by the Query Optimizer - which means that the QO decides on what is the best order to execute things.
For that reason, there is a rule that a UDF can not have any side effects - i.e. it cannot affect the state of (the data in) the database in any way. That has to be so - if a UDF were allowed to for instance delete rows from a table, than a query that uses that same table and uses the UDF would have different effects depending in what order the optimizer decides to execute the query. That kind of unpredictability is not acceptable in a database system. This is why in a UDF you cannot do any INSERT, UPDATE, DELETE, or MERGE.
Now if the UDF were allowed to call a stored procedure, then there is no practical way to guarantee that this stored procedure does not have any side effects. So in order to prevent that, Microsoft has decided to simply not allow calling stored procedures from a UDF. Even if that stored procedure does not have any side effect.
If you were asking out of interest, then I hope this answers your question.
If you were asking because you are stuck on a problem because of this, then perhaps now is the time to explain your problem to us and see if perhaps someone on this forum is able to find a different solution.
January 10, 2016 at 9:53 am
pr.nimbalkar (1/10/2016)
Hi Friends,Why does you can't call SP in UDF? Please tell.
UDF is the equivalent of a "select" statement, not possible to execute another statement in a separate scope within that initial select statement scope.
😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply