Question: What are the limitation of the User Defined Functions (UDF)?
The UDF has its own advantage and usage, but in this article we will see the limitation of UDF. Things UDF cannot do and why Stored Procedure are considered as more flexible than UDFs. Stored Procedure are more flexible than User Defined Functions (UDF).
- The UDF has No Access to Structural and Permanent Tables.
- UDF can call Extended Stored Procedure, which can have access to structural and permanent tables. (No Access to Stored Procedure)
- UDF Accepts Lesser Numbers of Input Parameters.
- UDF can have up to 1023 input parameters, Stored Procedure can have upto 21000 input parameters.
- UDF Prohibit Usage of Non-Deterministic Built-in Functions
- Functions GETDATE () etc. cannot be used UDFs, but can be used in Stored Procedure
- UDF Returns Only One Result Set or Output Parameter
- Due to this it can be used in a SELECT statement, but can not return multiple result set like Stored Procedure
- UDF cannot Call Stored Procedure
- Only access to Extended Stored Procedure.
- UDF cannot Execute Dynamic SQL, or Temporary Tables
- UDF cannot run dynamic SQL which is dynamically building in UDF. Temporary Tables can not be used in UDF as well.
- UDF cannot Return XML
- FOR XML is not allowed in UDF
- UDF does not support SET options
- SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)
- UDF does not Support Error Handling
- RAISEERROR or @@ERROR are not allowed in UDFs.
As I said earlier in this article is written to show Limitations of UDF. I use UDF for many reasons, the main reason I use it, I can do a repetitive task in SELECT statement as well as modularizing I frequently used code.
Reference : Pinal Dave (https://blog.sqlauthority.com)