Is EXECUTE allowed with in User Define Function (UDF) in SQL server?
No, EXECUTE is not allowed within UDF and that’s why you cannot run dynamic SQL or Dynamic Query in the function.
When you try to run the EXECUTE in UDF you will get the error message
“Invalid use of 'EXECUTE' within a function”
So why SQL is not alllwoing EXECUTE with in UDF ?
Basically there is two reason for this.
1) Trust
2) Performance
1) TRUST :
Basically UDF is not design for Insert, update and delete and if SQL allow EXECUTE then with in that anyone can run that command.
However you will thought that why Microsoft does not put validation on that like when you do Insert in EXECUTE and you run it from SP then it allowed and when you run it under UDF then they restrict you.
Yes it is possible but it will slow the process and kill the purpose of UDF.
2) Performance:
If Microsoft allowed EXECUTE then it will reduce the performance of UDF and whole idea behind UDF may not fulfill well.
So what should you do to use Dynamic Query ? you should use Store Procedure for that.
