{"id":7897,"date":"2020-02-10T17:45:33","date_gmt":"2020-02-11T00:45:33","guid":{"rendered":"https:\/\/database.guide\/?p=7897"},"modified":"2024-09-21T05:01:26","modified_gmt":"2024-09-20T19:01:26","slug":"create-a-scalar-user-defined-function-in-sql-server","status":"publish","type":"post","link":"https:\/\/database.guide\/create-a-scalar-user-defined-function-in-sql-server\/","title":{"rendered":"Create a Scalar User-Defined Function in SQL Server"},"content":{"rendered":"\n

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

This article contains examples of creating some basic T-SQL<\/a> scalar UDFs.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

First, let’s look at the syntax for creating scalar UDFs.<\/p>\n\n\n\n

The syntax for T-SQL scalar UDFs goes like this:<\/p>\n\n\n\n

 \nCREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name   \n( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type   \n    [ = default ] [ READONLY ] }   \n    [ ,...n ]  \n  ]  \n)  \nRETURNS return_data_type  \n    [ WITH <function_option> [ ,...n ] ]  \n    [ AS ]  \n    BEGIN   \n        function_body   \n        RETURN scalar_expression  \n    END  \n[ ; ]<\/code><\/pre>\n\n\n\n
And the syntax for CLR scalar UDFs:<\/p>\n\n\n\n
 \nCREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name   \n( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type   \n    [ = default ] }   \n    [ ,...n ]  \n)  \nRETURNS { return_data_type }  \n    [ WITH <clr_function_option> [ ,...n ] ]  \n    [ AS ] EXTERNAL NAME   \n[ ; ]  <\/code><\/pre>\n\n\n\n
The parts at <function_option><\/code> for T-SQL functions and <clr_function_option><\/code> for CLR functions allow you to specify options for the UDF. Function options include adding encryption, schema binding<\/a>, an EXECUTE AS<\/code> clause, as well as specifying what to do when a NULL value is passed as an argument.<\/p>\n\n\n\n
A full list of arguments and function options can be found on the Microsoft website<\/a>.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Example 1 – Basic Scalar UDF<\/h2>\n\n\n\n
Here’s an example the code used to create a basic T-SQL scalar UDF.<\/p>\n\n\n\n
CREATE FUNCTION dbo.ufn_discountPrice( \n    @price DECIMAL(12,2), \n    @discount DECIMAL(12,2) \n    ) \nRETURNS DECIMAL (12,2)\nAS\nBEGIN\n  RETURN @price * (1 - @discount);\nEND;<\/code><\/pre>\n\n\n\n
This scalar UDF accepts two parameters; @price<\/code> and @discount<\/code>. 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.<\/p>\n\n\n\n
Example 2 – Invoke the UDF<\/h2>\n\n\n\n
Once the UDF has been created, it can then be invoked within T-SQL code any time you need it.<\/p>\n\n\n\n
Here’s an example of invoking the UDF:<\/p>\n\n\n\n
SELECT dbo.ufn_discountPrice(100, .2) AS Result;<\/code><\/pre>\n\n\n\n
Result<\/p>\n\n\n\n
+----------+
| Result   |
|----------|
| 80.00    |
+----------+<\/pre>\n\n\n\n
Example 3 – Querying a Table<\/h2>\n\n\n\n
Scalar UDFs can also do stuff like query database tables.<\/p>\n\n\n\n
Here’s one that returns the number of albums are in the database for a given artist.<\/p>\n\n\n\n
CREATE FUNCTION dbo.ufn_CountAlbums (@ArtistId int)  \nRETURNS smallint\nAS  \nBEGIN  \n    DECLARE @AlbumCount int;\n    SELECT @AlbumCount = COUNT(AlbumId)\n    FROM Albums\n    WHERE ArtistId = @ArtistId; \n    RETURN @AlbumCount;\nEND;<\/code><\/pre>\n\n\n\n
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<\/a>, because table-valued functions return its results as a set of rows.<\/p>\n\n\n\n
Example 4 – Schema Binding<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
For example, you wouldn’t be able to drop a table that a schema bound UDF uses in its definition.<\/p>\n\n\n\n
To schema bind a UDF, use WITH SCHEMABINDING<\/code> in its definition. You also need to use two-part names for any objects that are referenced in the UDF.<\/p>\n\n\n\n
Here’s the previous example rewritten so that it’s schema bound:<\/p>\n\n\n\n
CREATE FUNCTION dbo.ufn_CountAlbums (@ArtistId int)  \nRETURNS smallint\nWITH SCHEMABINDING\nAS  \nBEGIN  \n    DECLARE @AlbumCount int;\n    SELECT @AlbumCount = COUNT(AlbumId)\n    FROM dbo.Albums\n    WHERE ArtistId = @ArtistId; \n    RETURN @AlbumCount;\nEND;<\/code><\/pre>\n\n\n\n
So, I changed two things from the first example. I added WITH SCHEMABINDING<\/code>, and I changed Albums<\/code> to dbo.Albums<\/code>.<\/p>\n\n\n\n
Now if someone tries to drop that table or make other changes to it, they’ll get an error.<\/p>\n\n\n\n
Example 5 – Encryption<\/h2>\n\n\n\n
You can also use WITH ENCRYPTION<\/code> to encrypt the function.<\/p>\n\n\n\n
CREATE FUNCTION dbo.ufn_CountAlbums (@ArtistId int)  \nRETURNS smallint\nWITH ENCRYPTION\nAS  \nBEGIN  \n    DECLARE @AlbumCount int;\n    SELECT @AlbumCount = COUNT(AlbumId)\n    FROM dbo.Albums\n    WHERE ArtistId = @ArtistId; \n    RETURN @AlbumCount;\nEND;<\/code><\/pre>\n\n\n\n
Example 6 – NULL Input<\/h2>\n\n\n\n
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<\/code> in the function’s definition.<\/p>\n\n\n\n
Specifying that option will return NULL if any of the arguments are NULL.<\/p>\n\n\n\n
CREATE FUNCTION dbo.ufn_CountAlbums (@ArtistId int)  \nRETURNS smallint\nWITH RETURNS NULL ON NULL INPUT\nAS  \nBEGIN  \n    DECLARE @AlbumCount int;\n    SELECT @AlbumCount = COUNT(AlbumId)\n    FROM dbo.Albums\n    WHERE ArtistId = @ArtistId; \n    RETURN @AlbumCount;\nEND;<\/code><\/pre>\n\n\n\n
When I invoke the function using NULL as the argument:<\/p>\n\n\n\n
SELECT dbo.ufn_CountAlbums(NULL) AS Result;<\/pre>\n\n\n\n
I get a different result, depending on what I’ve specified for this option.<\/p>\n\n\n\n
Here’s the result when the function uses the default setting (CALLED ON NULL INPUT<\/code>):<\/p>\n\n\n\n
+----------+
| Result   |
|----------|
| 0        |
+----------+<\/pre>\n\n\n\n
And here’s the result when it uses RETURNS NULL ON NULL INPUT<\/code>:<\/p>\n\n\n\n
+----------+
| Result   |
|----------|
| NULL     |
+----------+<\/pre>\n\n\n\n
Example 7 – Multiple Options<\/h2>\n\n\n\n
You can separate multiple options with a comma.<\/p>\n\n\n\n
Here’s an example that adds both encryption and schema binding to the function:<\/p>\n\n\n\n
CREATE FUNCTION dbo.ufn_CountAlbums (@ArtistId int)  \nRETURNS smallint\nWITH ENCRYPTION, SCHEMABINDING\nAS  \nBEGIN  \n    DECLARE @AlbumCount int;\n    SELECT @AlbumCount = COUNT(AlbumId)\n    FROM dbo.Albums\n    WHERE ArtistId = @ArtistId; \n    RETURN @AlbumCount;\nEND;<\/code><\/pre>\n\n\n\n
Example 8 – Alter a Function<\/h2>\n\n\n\n
You can alter a scalar UDF by replacing CREATE<\/code> with ALTER<\/code>.<\/p>\n\n\n\n
ALTER FUNCTION dbo.ufn_CountAlbums (@ArtistId int)  \nRETURNS smallint\nWITH SCHEMABINDING\nAS  \nBEGIN  \n    DECLARE @AlbumCount int;\n    SELECT @AlbumCount = COUNT(AlbumId)\n    FROM dbo.Albums\n    WHERE ArtistId = @ArtistId; \n    RETURN @AlbumCount;\nEND;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,5],"tags":[93,77,61,99],"class_list":["post-7897","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-functions","tag-mssql","tag-t-sql","tag-udf"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/7897","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=7897"}],"version-history":[{"count":4,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/7897\/revisions"}],"predecessor-version":[{"id":40331,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/7897\/revisions\/40331"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=7897"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=7897"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=7897"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}