Creating User-Defined Functions

User-defined functions are bodies of code/SQL designed to be reusable across a variety of possible consumers: stored procedures, applications, or even other functions. In that respect, they are no different from functions written in C# or Visual Basic. They are routines that can accept parameters and return a value. User-defined functions return scalar values (for example, a single value) or a resultset containing rows and columns of data.

One example of a user-defined function might be one that accepts a date and then determines whether the day is a weekday or weekend. Stored procedures or other functions in the database can then use the function as part of their processing.

Because user-defined functions are T-SQL statements with a format similar to stored procedures, the SQL Editor again is the primary tool for writing them. For each data connection visible in Server Explorer, a Functions folder contains any existing functions. To create a new function, you can right-click this folder, select Add New, and then select the type of function to create. You have three options:

Inline Function—Returns values as a resultset; the resultset is built from the results of a SELECT query.

Table-Valued Function—Returns values as a resultset; the resultset is built by programmatically creating a table within the function and then populating the table using INSERT INTO queries.

Scalar-Valued Function—Returns a single value.

After selecting the appropriate function type, template code for the function is delivered inside a new SQL Editor window. Feel free to use the query/view designer to construct any required lines of SQL within the function.

For the specifics on how to write a function and put it to best use within the database, consult your database’s documentation.