Tuesday, 6 December 2016

7 Limitation of User Defined Function [UDF]

7 Limitation of User Defined Function [UDF]

  • Can not Error Handling: RAISERROR statement cannot be used within a User Definded Function. You can not even check the value of the @@ERROR global variable within a function
  • Cannot Use Temporary Tables: User Definded Function can not make use of temporary tables. You are allowed to use table variables within a User Definded Function.
  • Cannot Execute Dynamic SQL: User Definded Function also cannot execute dynamically SQL statements. If you want to create a statement dynamically based on the parameter values, you should use stored procedures.
  • Cannot Call Stored Procedures: Stored procedures can not be call within User Definded Function.
  • Returns Only One Result Set: User Definded Function can return only one rowset to the user, whereas stored procedures can return multiple rowsets
  • Limitation of Using Cursors: User Definded Function cannot return data to the user through the FETCH keyword within a cursor.
  • Smaller Number of Parameters:- User Definded Function can have up to 1024 parameters, whereas stored procedures support up to 2100 parameters
  • No Use of Non-deterministic Built-in Functions: User Definded Function cannot use non-deterministic built-in functions. This rule prohibits usage of very common functions such as GETDATE() or RAND() in UDFs.

No comments:

Post a Comment

Note: only a member of this blog may post a comment.