SQL Server User Defined Function Example

By: Rick Dobson | Updated: 2019-11-01 | Comments | Related: > Functions User Defined UDF

Problem

SQL Server offers three types of user defined functions (udf) and in this tip we will cover examples for each of the three major types of user-defined function types: scalar-valued, table-valued and multi-statement table-valued.

Solution

This tip demonstrates a series of examples illustrating how to apply three different types of user-defined functions in SQL Server. A prior tip, SQL Server User Defined Function Overview, describes some general benefits of a user-defined function (udf) as well as the types and syntax for defining and invoking a udf. If you feel the need, refer to the prior tip for an introduction to core concepts and syntax conventions for user-defined functions. This tip complements the earlier one by providing working examples that you can use as models for applying a udf when refactoring old code or developing new code.

A udf is a code module that can compute a result or extract and return a subset of rows from a data source. In these ways, a udf resembles a stored procedure. However, a udf has different syntax conventions and use case scenarios than a stored procedure. An overview of stored procedures with examples and simple use cases is available from this prior tip.

SQL Server Scalar-Valued User Defined Function (UDF) Example

Before you can use a udf , you must initially define it. The create function statement lets you create a udf. For example, you can code a SQL expression to compute a scalar value inside the create function statement. When some code invokes the function, the computed value is the result of the function. As with other code modules, such as stored procedures and views, you must first drop any prior udf with the same name before creating a new one.

The following code block illustrates a way of conditionally dropping a udf in a schema of a database.

The use statement specifies CodeModuleTypes as the default database context. Without a use statement, the remainder of code would operate in the context of whatever database is the current database before the create function statement, such as the master database or another user-defined database instead of the CodeModuleTypes database.
A drop function statement is embedded after the use statement in the try segment of a try…catch block.
- The name for the function is udf_compute_line_total in the dbo schema.
- If there is a prior version of the function in the dbo schema of the current database context, the drop function statement succeeds, and the try…catch block is exited.
- If the drop function statement fails, control passes to the catch segment of the block where a select statement displays the error number and error message; you can use this feedback to help diagnose the nature of any problem. A common type of error is an error resulting from trying to drop a udf that does not exist. You can ignore this kind of error; this error typically means there is no prior version of the udf to be deleted. You should remedy other kinds of errors because they indicate that an existing udf was not dropped. This can cause a subsequent create function statement to fail.

use CodeModuleTypes
go
 
-- conditionally drop inline udf without specifying the udf type
-- also demonstrate a general try...catch approach that works 
-- for other types of objects besides user-defined functions
begin try
   drop function [dbo].[udf_compute_line_total]
end try
begin catch
   select 
       error_number() [Error Number]
      ,error_message() [Error Message]
end catch

Here's a code block to define a fresh version of udf_compute_line_total in the dbo schema. The udf computes the total price for a detail line in an order based on the quantity ordered, the standard unit price for the item, and any discount applied to the standard unit price for the line item within the order.

The first line of code begins with create function. This term tells SQL Server to start a new udf.
- The rest of the create function statement tells SQL Server which type of udf to create.
- Immediately following the create function term is a two-part name for the scalar-valued udf created in this section. The first part is for the schema (dbo), and the second part is for udf name (udf_compute_line_total).
The next line of code includes a list of parameters in parentheses for the function.
- Parameters enable a udf to operate for different sets of data.
- Each parameter must have a name that starts with an @.
- Follow the parameter name with a data type; this example uses
  - the int data type for the @OrderQty parameter
  - the money data type for the @UnitPrice parameter
  - the float data type for the @Unitpricediscount parameter
- If there are no parameters for a udf, then you must follow the udf name by an empty pair of parentheses. Without parameters, the name term after create function needs to be denoted as dbo.udf_compute_line_total().
The returns clause for a scalar-valued udf designates the data type for the result returned by the function.
An as keyword delimits the function declarations for the function name and parameter(s) from the code specifying how to compute the function's result.
The expression for computing the value returned by a scalar-valued udf occurs within a begin…end block. Within the block is a return clause that passes a computed value to the code calling the function. The returned value has the data type specified by the returns clause.
In the case of a scalar-valued udf, the code inside the return clause can be an expression that returns a value. The computed value is ultimately returned from the function in the data type specified by the returns clause. Before the returns clause ultimately configures the data type for a return value multiple data type conversions can occur within the function. For example,
- The @OrderQty * @UnitPrice term returns a value in a money data type. The @UnitPrice parameter has a money data type and the @OrderQty term has an int data type.
- @Unitpricediscount is 0.0 or some value less than or equal to 1.0000. For example, a @Unitpricediscount value of 0.02 means the product of OrderQty and UnitPrice is multiplied by .98 with a float data type so that the resulting value has a float data type.
- Finally, the udf transforms the float data type into a numeric (38,6) data type because of its returns clause setting. This setting is for a decimal value with up to 38 characters and as many as six charters after the decimal point.

-- create function scalar-valued udf
create function dbo.udf_compute_line_total (@OrderQty int, @UnitPrice money, @Unitpricediscount float)
returns numeric(38,6)
as
begin
    return ((@OrderQty * @UnitPrice) * (1 - @Unitpricediscount))
end

You can pass parameters to a scalar-valued function at run time via local variables or with the columns of a data source for a select statement. You can display a return value from a function with a select statement.

The following script shows examples of how to pass values to a function with local variables.

The declare statement at the top of the script instantiates three local variables named @OrderQty, @UnitPrice, and @Unitpricediscount.
The next three set statements assign values to the local variables.
Then, a select statement displays the three local variables trailed by an invocation of the udf_compute_line_total scalar-valued function.
The next three set statements along with another trailing select statement specifies a different set of parameters for the udf_compute_line_total scalar-valued function. This set of values include a non-zero value for @Unitpricediscount.

-- declare local variables for input parameters
declare @OrderQty int, @UnitPrice money, @Unitpricediscount float
 
-- assign values to input parameters
set  @OrderQty = 3
set  @UnitPrice = 2024.994
set  @Unitpricediscount = 0
 
select 
     @OrderQty [OrderQty]
    ,@UnitPrice [UnitPrice]
    ,@Unitpricediscount [Unitpricediscount]
    ,dbo.udf_compute_line_total(@OrderQty, @UnitPrice, @Unitpricediscount) [Line Total]
 
-- assign value to input parameters
set  @OrderQty = 12
set  @UnitPrice = 1971.9942
set  @Unitpricediscount = 0.02
 
select 
     @OrderQty [OrderQty]
    ,@UnitPrice [UnitPrice]
    ,@Unitpricediscount [Unitpricediscount]
    ,dbo.udf_compute_line_total(@OrderQty, @UnitPrice, @Unitpricediscount) [Line Total]

Here's the output from the preceding script. The three parameter values are followed by the function's result (Line Total). The top pane shows the results set for the first select statement, and the bottom pane shows the results set for the second select statement.

The following script demonstrates how to pass values to a scalar-value udf from the columns of a data source for a select statement.

The data source is the SalesOrderDetails table in the Sales schema of the AdventureWorks2014 database.
This data source is referenced in the from clause of a select statement.
In this demonstration, there are two select statements whose results sets are concatenated by a union operator.
- The first select statement displays a subset of the columns from the top two SalesOrderDetail rows whose UnitPriceDiscount value equals zero.
- The second select statement displays the same subset of the columns from the top two SalesOrderDetail rows whose UnitPriceDiscount value does not equal zero.
- The top operator within both select statements restricts the output to the first two rows of the results set from each select statement.

-- original source data for input parameters
select top 2 
       [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal] [Line Total from table]
      ,dbo.udf_compute_line_total(OrderQty, UnitPrice, UnitPriceDiscount) [Line Total from function]
from [AdventureWorks2014].[Sales].[SalesOrderDetail]
where [UnitPriceDiscount] = 0
 
union
 
select top 2 
       [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal] [Line Total from table]
   ,dbo.udf_compute_line_total(OrderQty, UnitPrice, UnitPriceDiscount) [Line Total from function]
from [AdventureWorks2014].[Sales].[SalesOrderDetail]
where [UnitPriceDiscount] != 0

Here's the output from the preceding script. The next-to-last column shows the Line Total value for a row from the SalesOrderDetail table. The last column shows the computed Line Total value from the dbo.udf_compute_line_total function.

The first two rows are from the select statement before the union operator.
The second two rows are from the select statement after the union operator.
The second and third rows from the following results set match the parameter values and output from the preceding results set.
As you can see, the udf returns identical results whether its parameters are assigned values with local variables or with columns from a data source.
Also, the udf returns identical Line Total values to those in the SalesOrderDetail table.

SQL Server Inline Table-Valued User Defined Function (UDF) Example

A table-valued udf returns a rowset instead of just a single value as is the case for a scalar-valued udf. An inline table-valued udf is a kind of udf that depends on just one select statement. You can reference an inline table-valued udf just like a table in the from clause of a select statement.

As with other udf types, you should conditionally drop an inline table-valued udf if there is a another udf with the same name in the same schema of a database. The syntax for dropping an inline table-valued udf is the same as for a scalar-valued udf.

Here's a script to create a fresh copy of an inline table-valued function after any prior version of the udf is dropped from the dbo schema in the default database, which is CodeModuleTypes for this tip demonstration.

The udf_compute_line_total_rowset is an inline table-valued udf.
- Because there are no parameters for this function, an empty pair of parentheses is appended to the udf name after create function (dbo.udf_compute_line_total_rowset()).
- As you can see, the code uses a two part name, which is mandatory for create function statements in SQL Server.
The returns clause passes its value back to the calling code as a table object.
The select statement in the return clause specifies the rowset to be returned from the SalesOrderDetail table in the Sales schema of the AdventureWorks2014 database.
There are two columns of output.
- SalesOrderDetailID serves as the primary key for the source table. In other words, each output row is for a unique row in the SalesOrderDetail table.
- A computed field with an alias of LineTotal_w_udf_compute_line_total_rowset. It is worth noting that the data type for the field is money because OrderQty has an int data type, but both UnitPrice and unitpricediscount have a money data type in the SalesOrderDetail table. The money data type only displays numbers for up to four places after the decimal point.
The where clause filters for rows with unitpricediscount that is not equal to zero. This facilitates evaluating the function for rows in which there is a discount from the standard UnitPrice for the item in the detail line of an order.

-- create an inline table-value function
create function dbo.udf_compute_line_total_rowset()
returns table
as
return
(
select [SalesOrderDetailID]
      ,[OrderQty]*[UnitPrice]*(1-unitpricediscount) [LineTotal_w_udf_compute_line_total_rowset]
from [AdventureWorks2014].[Sales].[SalesOrderDetail]
where unitpricediscount != 0
)

Here's another script that compares the inline table-valued function from this section with the scalar-valued udf from the previous section. The script also displays the LineTotal column from the source table as an additional point of reference. Output is filtered by a where clause that only includes rows containing a non-zero value in the UnitPriceDiscount column.

-- comparison of LineTotal from table versus inline table-valued udf
-- and scalar-valued udf
select 
    a.SalesOrderDetailID
   ,LineTotal [Line Total from table] 
   ,b.LineTotal_w_udf_compute_line_total_rowset
   ,dbo.udf_compute_line_total([OrderQty],[UnitPrice],[UnitPriceDiscount]) [Line Total from scalar-valued udf] 
from [AdventureWorks2014].[Sales].[SalesOrderDetail] a
left join dbo.udf_compute_line_total_rowset() b
on a.SalesOrderDetailID = b.SalesOrderDetailID
where UnitPriceDiscount != 0

Because the inline table-valued udf returns a table, you can process its output like a table. You can reference this type of udf by itself in the from clause of a select statement. For example, this select statement (select * from dbo.udf_compute_line_total_rowset()) displays all the columns for all rows in the returned udf rowset. The preceding script illustrates a join between an inline table-valued udf and the SalesOrderDetail table from the AdventureWords2014 database.

The select statement left joins the inline table-valued udf (dbo.udf_compute_line_total_rowset()) to the SalesOrderDetail table in the Sales schema of the AdventureWorks2014 database by SaleOrderID values from each source.
The SalesOrderDetail table has an alias of a.
The udf_compute_line_total_rowset() has an alias of b.

An excerpt from the output of the preceding script appears in the next screen shot. The excerpt shows the first ten rows from the results set for the preceding query code window. The LineTotal values from the table perfectly match LineTotal values from the scalar-valued udf. On the other hand, the LIneTotal values from the inline table-valued udf precisely correspond to the source table only up to the first three places after the decimal point. The value of the fourth digit after the decimal point is rounded if there are more than four characters after the decimal point. The rounding to four places after the decimal is because the LIneTotal value from the inline table-valued udf has a money data type for LineTotal. On the other hand, the scalar-valued function returns LineTotal with a numeric(38,6) data type; rounding is performed only for computed values with more than six places after the decimal point. Numeric(38,6) is the same data type as the one specified for the LineTotal column of the SalesOrderDetail table in the AdventureWorks2014 database.

SQL Server Multi-Statement Table-Valued User Defined Function (UDF) Example

A multi-statement table-valued udf returns a rowset populated by two or more T-SQL statements from within the udf. The internal rowsets are concatenated into a table variable for output from the udf. Learn more about table variables from these two MSSQLTips.com tips (here and here). Because the output from a multi-statement table-valued udf is a table variable, you can reference it in a from clause and join it with tables. However, multi-statement table-valued user-defined functions are widely regarded as performance killers with medium to large datasets. A prior MSSQLTips.com tip indicates the poor performance is linked to version of SQL Server being used; more specifically, the 2017 version of SQL Server overcomes the issue associated with earlier versions of SQL Server. Therefore, unless a multi-statement table-valued udf runs exclusively on SQL Server 2017 consider restricting its use to relatively small data sets, such as rowsets with row counts of not much more than 100.

The following script shows the create function statement for a multi-statement table-valued udf. The udf enumerates the stored procedures and user-defined functions in a database. It is again assumed the code operates in the CodeModuleTypes database, but you can use any database with a selection of stored procedures and user-defined functions within it. Notice that the function's name is udf_routine_types in the dbo schema. Run a drop function statement if a udf with this name already exists in the dbo schema.

The function name appears after create function. Because this udf does not take any parameters, an empty pair of parentheses denotes the end of the function name.
The returns clause designates the names of the table variable and its columns along with the data types for the columns. This table variable (@routine_types) is returned by the udf.
This udf has two insert into statements that can successively add rows to @routine_types. The first set of rows are for enumerating user-defined functions in the default database context. The second set of rows are for enumerating stored procedures in the default database context.
- The data source for both first and second select statements is information.schema.routines. MSSQLTips.com offers a tutorial on information.schema views. Additionally, a prior MSSQLTips.com tip drills down on how to track stored procedures with the information.schema.routines view.
- The where clause for the first query includes rows with a routine_type value of function.
- The where clause for the second query includes rows with a routine_type value of procedure.

-- create a multi-statement table-valued udf
create function dbo.udf_routine_types()
 
returns @routine_types TABLE (
        [database name] varchar(max),
        [schema name] varchar(max),
        [routine name] varchar(max),
        [routine type] varchar(max)
    )
as
 
begin
 
   insert into @routine_types
   -- list function routine types
   select 
      specific_catalog [database name]
     ,specific_schema [schema name]
     ,specific_name [routine name]
     ,routine_type [routine type]
   from information_schema.routines  
   where routine_type = 'function';
 
   insert into @routine_types
   -- list stored procedure routine types
   select 
      specific_catalog [database name]
     ,specific_schema [schema name]
     ,specific_name [routine name]
     ,routine_type [routine type]
   from information_schema.routines  
   where routine_type = 'procedure'
 
   return;
 
end;

To view the contents of a multi-statement udf, you can invoke a select statement like the following one.

-- select from a multi-statement udf
select * from dbo.udf_routine_types()

Here's what the select statement displays as of the time for authoring this tip with the CodeModuleTypes database; recall the database was used for a series of tips on code modules. As you can see, there are ten code modules in the database. Five user-defined functions are enumerated first with a routine type value of FUNCTION. The remaining five rows with a routine type value of PROCEDURE enumerate the stored procedures in the CodeModuleTypes database.

Next Steps

The code samples from this tip rely on T-SQL select statements inside of user-defined functions. Therefore, you can run the code as is, and it should generate legitimate results. When the select statements reference the AdventureWorks database, you should obtain precisely the same result. When the select statements reference the CodeModuleTypes database, you may obtain slightly different results depending on how you configured the database with user-defined functions and stored procedures.

After you confirm that the code samples return valid values, change the database to others that are appropriate for requirements. If the precise queries within this tip do not address your needs, then change the queries based on the specifications that you are addressing. For example, specify a new data source and new output values or columns.

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2019-11-01