The Anatomy of a Function

The purpose of a function is to return a value. Most of the functions you will use return a scalar value, meaning a single unit of data, or a simple value. However, functions can return practically any data type, and this includes types such as Table and Cursor, which could be used to return entire, multi-row result sets. I won't take the discussion to that level in this chapter. Chapter 13 explains how to create and utilize user-defined functions to return more complex data.

Functions have been around for a long time, even long before SQL. The pattern used to call functions is the same in nearly all programming languages:

Result = Function()

In Transact-SQL, values are returned using the SELECT statement. If you just want to return a value in a query, you treat the SELECT as the output operator without using an equals sign:

SELECT Function()

I'd Like to Have an Argument

When it comes to SQL functions, the term argument is used to mean an input variable or placeholder for a value. Functions can have any number of arguments and some arguments are required whereas others are optional. Optional arguments are typically at the end of the comma-delimited argument list, making them easier to exclude if they are not to be provided in the function call.

When you read about functions in SQL Server Books Online or on-line help, you'll see optional arguments denoted in square brackets. In this example for the CONVERT() function, both the length argument for the data type and the style argument for the CONVERT() function are optional:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) 

I'll simplify this because we're really not discussing how to use data types at the moment:

CONVERT ( data_type, expression [ , style ] ) 

According to this, the CONVERT() function will accept either two or three arguments. So, either of these examples would be acceptable:

 SELECT CONVERT(VarChar(20), 'April 29, 1988') SELECT CONVERT(VarChar(20), 'April 29, 1988', 101) 

The first argument for this function is the data type, VarChar(20), and the second argument is the value, 'April 29, 1988'. The third argument in the second statement determines the style for numeric and date types. Even if a function doesn't take an argument, or doesn't require an argument, it is called with a set of empty parentheses. Note that when a function is referred to by name throughout the book, the parentheses are included because this is considered standard form.

Deterministic Functions

Because of the inner-workings of the database engine, SQL Server has to separate functions into two different groups based on what's called determinism. This is not a new-age religion. It's simply a statement about whether the outcome of a function can be predicted based on its input parameters or by executing it one time. If a function's output is not dependent on any external factors, other than the value of input parameters, it is considered to be a deterministic function. If the output can vary based on any conditions in the environment or algorithms that produce random or dependent results, the function is nondeterministic. Why make a big deal about something that seems so simple? Well, nondeterministic functions and global variables can't be used in some database programming objects such as user-defined functions. This is partially due to the way SQL Server caches and precompiles executable objects. For simple, ad-hoc queries, knock yourself out and use any type of function you like, but if you plan on building more advanced, reusable programming objects, it's important to understand this distinction. As a brief example, these functions are deterministic:

• AVG() (all aggregate functions are deterministic)

• CAST()

• CONVERT()

• DATEADD()

• DATEDIFF()

• ASCII()

• CHAR()

• SUBSTRING()

These functions and variables are nondeterministic:

• GETDATE()

• @@ERROR

• @@SERVICENAME

• CURSORSTATUS()

• RAND()

  Note

  You can find a complete list of all functions and their determinism in Appendix B.

Using Variables with Functions

Variables can be used for both input and output. In Transact-SQL, a variable is prefixed with the @ symbol, declared as a specific data type, and can then be assigned a value using either the SET or SELECT statements. The following example shows the use of an Int type variable called @MyNumber, passed to the SQRT() function:

 DECLARE @MyNumber Int SET @MyNumber = 144 SELECT SQRT(@MyNumber) 

The result of this call is 12, the square root of 144.

Using SET to Assign Variables

The following example uses another Int type variable, @MyResult, to capture the return value for the same function. This technique is most like the pattern used in procedural programming languages:

 DECLARE @MyNumber Int, @MyResult Int  SET @MyNumber = 144 -- Assign the function result to the variable:  SET @MyResult = SQRT(@MyNumber) -- Return the variable value SELECT @MyResult 

Using SELECT to Assign Variables

The same result can be achieved using a variation of the SELECT statement. A variable is declared prior to assigning a value. The chief advantage of using the SELECT statement instead of the SET command is that multiple variables can be assigned values in a single operation. The value is assigned using the SELECT statement and then can be used for any purpose after this script has been executed:

 DECLARE @MyNumber1 Int, @MyNumber2 Int, @MyResult1 Int, @MyResult2 Int  SELECT @MyNumber1 = 144, @MyNumber2 = 121 -- Assign the function result to the variable: SELECT @MyResult1 = SQRT(@MyNumber1), @MyResult2 = SQRT(@MyNumber2)  -- Return the variable value SELECT @MyResult1, @MyResult2 

Functionally, these techniques are identical; however, populating multiple variables with a SELECT statement is a great deal more efficient in regards to server resources than multiple SET commands. The limitation of selecting multiple or even single values into parameters is that the population of variables cannot be combined with data retrieval operations. This is why the preceding example used a SELECT statement to populate the variables followed by a second SELECT statement to retrieve the data in the variables. For example, the following script will not work:

 DECLARE @ContactName VarChar(65)  SELECT @ContactName = FirstName + ' ' + LastName, Phone FROM Contact WHERE ContactID = 3 

This script will generate the following error:

 Msg 141, Level 15, State 1, Line 2  A SELECT statement that assigns a value to a variable must not be combined with  data-retrieval operations. 

Using Functions in Queries

Functions are often combined with query expressions to modify column values. This is easily done by passing column names to function arguments. The function reference is inserted into the column list of a SELECT query, like this:

 SELECT FirstName, LastName, YEAR(BirthDate) AS BirthYear  FROM Employee 

In this example, the BirthDate column value is passed into the YEAR() function as an argument. The function's result becomes the aliased column BirthYear.

Nested Functions

Often, you will find that the functionality you need doesn't exist in a single function. By design, functions are intended to be simple and focused on providing a specific feature. If functions did a lot of different things, they would be complicated and difficult to use (and some are, but fortunately, not many). For this and other reasons, each function simply does one thing. To get all of the functionality I need, I may pass the value returned from one function into another function. This is known as a nested function call. Here's a simple example: The purpose of the GETDATE() function is to return the current date and time. It doesn't return elegantly formatted output; that's the job of the CONVERT() function. To get the benefit of both functions, I pass the output from the GETDATE() function into the value argument of the CONVERT() function, like this:

 SELECT CONVERT(VarChar(20), GETDATE(), 101) 

You'll see a few examples of this pattern throughout this chapter.