User-defined Functions in SQL

A function can be defined as a set of instructions that performs a specific task automatically, and can be called within the SQL instructions. Broadly speaking, we can classify two large groups or categories of functions:

  • Built-in functions: These are the system’s own functions, already defined by SQL Server. To find out more you can consult the built-in functions.

  • User-defined functions: These are functions created by the user to define their own and personalized functionality.

In this section, we will focus on explaining the characteristics and types of user-defined functions, as well as their creation, alteration, deletion, and execution within the SQL Server environment.

As we have mentioned before, user-defined functions perform custom operations according to the input parameters defined by the user, which encompasses extensive functionality limited only to certain restrictions that we will see later.

Based on the returned result, we can differentiate between two types of user-defined functions:

1. Scalar functions

Return a single scalar value whose data type will be defined in the RETURN clause

2. Table functions

They return a result set in the form of a table

The following data types: text, ntext, image, cursor and timestamp cannot be returned by scalar functions.

Features

  • They allow the creation and use of independent code blocks stored in the database that can be called in an unlimited way. This aspect is called: modular programming.

  • Unlike built-in functions, user-defined functions are malleable and modifiable.

  • They reduce the compilation time becoming an important tool to improve the efficiency in the execution of Transact SQL code.

  • They allow nesting. A user-defined function can be called from a different user-defined function.

Limitations

  • They cannot carry out operations that alter or modify the database such as INSERT INTO, UPDATES…

  • They cannot return multiple result sets.

  • They do not support ordinary error handling. This includes flow control structures: TRY...CATCH.

  • They cannot call stored procedures. This rules out extended stored procedures.