Create a Scalar User-Defined Function in SQL Server

Posted on by Ian

In SQL Server, you can create a scalar user-defined function using the CREATE FUNCTION statement. A scalar user-defined function, otherwise known as a scalar UDF, is a user-defined function that returns a single value.

This article contains examples of creating some basic T-SQL scalar UDFs.

Syntax

First, let’s look at the syntax for creating scalar UDFs.

The syntax for T-SQL scalar UDFs goes like this:

 
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type   
    [ = default ] [ READONLY ] }   
    [ ,...n ]  
  ]  
)  
RETURNS return_data_type  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN scalar_expression  
    END  
[ ; ]

And the syntax for CLR scalar UDFs:

 
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name   
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
)  
RETURNS { return_data_type }  
    [ WITH <clr_function_option> [ ,...n ] ]  
    [ AS ] EXTERNAL NAME   
[ ; ]

The parts at <function_option> for T-SQL functions and <clr_function_option> for CLR functions allow you to specify options for the UDF. Function options include adding encryption, schema binding, an EXECUTE AS clause, as well as specifying what to do when a NULL value is passed as an argument.

A full list of arguments and function options can be found on the Microsoft website.

The Microsoft documentation contains a lot of detail, so the following examples are aimed at providing a quick overview of some common concepts and options when creating scalar UDFs.

Example 1 – Basic Scalar UDF

Here’s an example the code used to create a basic T-SQL scalar UDF.

CREATE FUNCTION dbo.ufn_discountPrice( 
    @price DECIMAL(12,2), 
    @discount DECIMAL(12,2) 
    ) 
RETURNS DECIMAL (12,2)
AS
BEGIN
  RETURN @price * (1 - @discount);
END;

This scalar UDF accepts two parameters; @price and @discount. These are passed in to the function as arguments whenever the function is invoked. The function takes the value of those arguments, performs a calculation using those values, then returns the resulting value. In this case, the discounted price is returned.

Example 2 – Invoke the UDF

Once the UDF has been created, it can then be invoked within T-SQL code any time you need it.

Here’s an example of invoking the UDF:

SELECT dbo.ufn_discountPrice(100, .2) AS Result;

Result

+----------+
| Result   |
|----------|
| 80.00    |
+----------+

Example 3 – Querying a Table

Scalar UDFs can also do stuff like query database tables.

Here’s one that returns the number of albums are in the database for a given artist. 

CREATE FUNCTION dbo.ufn_CountAlbums (@ArtistId int)  
RETURNS smallint
AS  
BEGIN  
    DECLARE @AlbumCount int;
    SELECT @AlbumCount = COUNT(AlbumId)
    FROM Albums
    WHERE ArtistId = @ArtistId; 
    RETURN @AlbumCount;
END;

This is a scalar function because it returns a single value. If we wanted to return a list of albums, then we’d need to use a table-valued function, because table-valued functions return its results as a set of rows.

Example 4 – Schema Binding

When you create a user-defined function that depends on other objects in the database, it’s usually a good idea to schema bind the UDF. Schema binding the UDF ensures that no changes can be done to the underlying objects that could potentially impact the function.

For example, you wouldn’t be able to drop a table that a schema bound UDF uses in its definition.

To schema bind a UDF, use WITH SCHEMABINDING in its definition. You also need to use two-part names for any objects that are referenced in the UDF.

Here’s the previous example rewritten so that it’s schema bound:

CREATE FUNCTION dbo.ufn_CountAlbums (@ArtistId int)  
RETURNS smallint
WITH SCHEMABINDING
AS  
BEGIN  
    DECLARE @AlbumCount int;
    SELECT @AlbumCount = COUNT(AlbumId)
    FROM dbo.Albums
    WHERE ArtistId = @ArtistId; 
    RETURN @AlbumCount;
END;

So, I changed two things from the first example. I added WITH SCHEMABINDING, and I changed Albums to dbo.Albums.

Now if someone tries to drop that table or make other changes to it, they’ll get an error.

Example 5 – Encryption

You can also use WITH ENCRYPTION to encrypt the function. 

CREATE FUNCTION dbo.ufn_CountAlbums (@ArtistId int)  
RETURNS smallint
WITH ENCRYPTION
AS  
BEGIN  
    DECLARE @AlbumCount int;
    SELECT @AlbumCount = COUNT(AlbumId)
    FROM dbo.Albums
    WHERE ArtistId = @ArtistId; 
    RETURN @AlbumCount;
END;

Example 6 – NULL Input

When invoking the function, if any of the arguments is NULL, the function’s body is still executed. That is, unless you’ve explicitly stated RETURNS NULL ON NULL INPUT in the function’s definition.

Specifying that option will return NULL if any of the arguments are NULL.

CREATE FUNCTION dbo.ufn_CountAlbums (@ArtistId int)  
RETURNS smallint
WITH RETURNS NULL ON NULL INPUT
AS  
BEGIN  
    DECLARE @AlbumCount int;
    SELECT @AlbumCount = COUNT(AlbumId)
    FROM dbo.Albums
    WHERE ArtistId = @ArtistId; 
    RETURN @AlbumCount;
END;

When I invoke the function using NULL as the argument:

SELECT dbo.ufn_CountAlbums(NULL) AS Result;

I get a different result, depending on what I’ve specified for this option.

Here’s the result when the function uses the default setting (CALLED ON NULL INPUT):

+----------+
| Result   |
|----------|
| 0        |
+----------+

And here’s the result when it uses RETURNS NULL ON NULL INPUT:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

Example 7 – Multiple Options

You can separate multiple options with a comma.

Here’s an example that adds both encryption and schema binding to the function:

CREATE FUNCTION dbo.ufn_CountAlbums (@ArtistId int)  
RETURNS smallint
WITH ENCRYPTION, SCHEMABINDING
AS  
BEGIN  
    DECLARE @AlbumCount int;
    SELECT @AlbumCount = COUNT(AlbumId)
    FROM dbo.Albums
    WHERE ArtistId = @ArtistId; 
    RETURN @AlbumCount;
END;

Example 8 – Alter a Function

You can alter a scalar UDF by replacing CREATE with ALTER.

ALTER FUNCTION dbo.ufn_CountAlbums (@ArtistId int)  
RETURNS smallint
WITH SCHEMABINDING
AS  
BEGIN  
    DECLARE @AlbumCount int;
    SELECT @AlbumCount = COUNT(AlbumId)
    FROM dbo.Albums
    WHERE ArtistId = @ArtistId; 
    RETURN @AlbumCount;
END;