Scalar-Valued Functions

If built-in functions do not meet all of our needs, we can create custom functions with T-SQL. User-defined functions can accept zero or more parameters and return either a scalar value or a table. In this lesson, we’ll focus on scalar-valued functions, which are functions that return a single value.

Creating user-defined functions

New functions can be created using the CREATE FUNCTION command. Because a function is a database object, it must be created within some database. A function may or may not accept parameters. The complete syntax for declaring a function is:

CREATE FUNCTION FunctionSchema.FunctionName(@ParameterName [Parameter data type], ...)
RETURNS [Type of returned value]
AS
BEGIN
    [Function body here]
    RETURN [Value to return]
END;

As we can see, the syntax is comparable to function declaration in procedural programming languages.

Returning scalar values

Scalar-valued functions return a single value. Let’s consider the following example. We want to create a function that will calculate the square of an integer and return the result as an integer value. Our function declaration would look like this:

CREATE FUNCTION dbo.CalculateSquare(@Number INT)
RETURNS INT
AS
BEGIN
    [Function body here]
    RETURN [Value to return]
END;

The @Number parameter, in the snippet above, is required for this function to run. The data type of this parameter is INT, which means we must provide an INT value when calling this function. Let’s complete the function and run it using a sample value: