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;