Question

Pros and Cons of SQL Functions with examples

Answer 1

Pros of Functions in SQL :

• A set of statements can be reused in many places.
• Functions can be created according to the need of user.
• It can be used with inline queries.
• There is no restriction when comparing to stored procedures.
• Complexity is reduced.
• Easier to read and write.
• We can pass parameters to the functions, parameters can hold different values in different calls to function.
• They are more easier to invoke.
• They can be used in situations like joining, where , select statements etc..

Cons of Functions in SQL :

• We cannot use the functions defined by user on the base table.
• It can return only a row at a time.
• Can only pass smaller number of parameters when comparing to stored procedures.
• It cannot be used to return XML string.
• If we encounter an error when executing a function , the execution of the function stops.
• The global variable @@ERROR will not work inside a user defined function.
• RAISERROR statement can't be used in a function.
• Temporary tables cannot be used.
• We cannot update the value of a field using SET statement inside a function.
• It cannot be used to execute dynamically created statements.
• A stored procedure cannot be called from inside of a user defined functions.