UDF have its own advantage and usage but in this article we will see the limitation of UDF. Things UDF cannot do and why Stored Procedure is considered as more flexible then UDFs. Stored Procedure is more flexibility then User Defined Functions (UDF).
- 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 upto 1023 input parameters; Stored Procedure can have upto 21000 input parameters.
- UDF does not Support Error Handling.
- RAISEERROR or @@ERROR are not allowed in UDFs.
- UDF Returns Only One Result Set or Output Parameter.
- Due to this it can be used in SELECT statement but cannot 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 are dynamically built in UDF. Temporary Tables cannot 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 is used for many reasons, the main reason I use it I can do repetitive task in SELECT statement as well as modularizing my frequently used code.