By: Rick Dobson | Updated: 2019-10-08 | Comments | Related: > Functions User Defined UDF
Problem
What are the different types of SQL Server user-defined functions? What are special syntax rules for creating and invoking each major type of SQL Server user-defined function?
Solution
Like a stored procedure, a user-defined function (UDF) lets a developer encapsulate T-SQL code for easy re-use in multiple applications. Also, like a stored procedure, the scope of a UDF is the entire database in which it resides, and its definition is tied to a specific schema within a database. A reference to a UDF requires, with one exception, a schema qualifier (shcema_name.udf_name). The exception is for the scenario in which a UDF populates a local variable in an exec statement. In contrast, a schema qualifier is a best practice but not a requirement for referencing a stored procedure (schema_name.stored_procedure_name or stored_procedure_name).
A UDF can return a scalar value or a rowset. You can assign parameter values at run time for user-defined functions; SQL Server accepts these parameters to provide values so a SQL expression can return a scalar value or a select statement can return rowsets.
You can reference a scalar-valued UDF in a SELECT statement as a list item as well as in WHERE and HAVING clauses. You can also use an EXEC statement to transfer a UDF return value to a local variable. A rowset from a user-defined function can be displayed via a SELECT statement by referencing a UDF in its FROM clause.
Ways of classifying user-defined functions
There are three primary types of user-defined functions. This tip presents templates for implementing each of these UDF types.
- A scalar-valued UDF returns a single value, such as the value in US dollars required to purchase an item originally priced in British pounds.
- An inline table-valued UDF returns a table object based on a single SELECT statement that is contained within a return clause. An inline function might return the name, gender, and age of employees within a department in the AdventureWorks database.
- A multi-statement table-valued user-defined function returns a rowset based on a table variable. The table variable can be populated by two or more T-SQL statements, such as two or more SELECT statements that successively add rows to a table variable. Alternatively, a single INSERT can draw row values from a common table expression (cte). The code for populating the output table variable must reside within a Begin…End block.
User-defined functions can be either created with schemabinding or not. You can learn more about schemabinding from these prior tips (here and here). Schemabinding is of special value to UDF users because it can cause functions to run faster, reduce the possibility of failures from changes to the structure of underlying data sources, and block changes from altering data that needs to be read before any alterations are made to them. Additionally, when a schema-bound UDF includes a reference to a view, the view must also be schema-bound.
If it becomes necessary to change one or more referenced data source(s) for a UDF with schemabinding, you can make the change with the following steps.
- Invoke an alter function statement for the UDF but omit the specification for schemabinding.
- Make your required changes to the referenced data source(s).
- Again, invoke an ALTER FUNCTION statement for the UDF to
- Reflect any UDF changes that need to be made based on changes to its underlying data source(s)
- Add the specification for schemabinding to the UDF
Syntax conventions for scalar-valued functions
When creating a scalar-valued UDF, you can use the following syntax. The T-SQL is presented as a template with pseudo code for you to update when you decide to apply the template in a specific context.
- The template commences with a USE statement for any user-defined database; replace Database_name with the name of the database in which you want to create a UDF.
- The user account for creating the UDF can be for a sysadmin login, a database
owner, or any database user account with database connection permission and
up to three additional permissions. The additional permissions are:
- Create function permission
- Alter permission within the schema in which the function is defined
- Execute permission on a user-defined type referenced in the UDF
- Next, the code derives the object_id value for a scalar-valued UDF in a schema if it exists. If the object_id is not null, the UDF object is dropped from the database with a drop function statement.
- Next, a create function statement is described for creating a fresh copy of the UDF named udf_name in the schema named schema_name.
- Review the comments for an explanation of the elements within a create function
statement for a scalar-valued UDF.
- Keep the keywords, such as returns and return unchanged.
- Use the actual UDF name and schema name for the UDF being created.
- Substitute T-SQL code in the begin…end block for computing a scalar value. This code can be either a T-SQL expression or a select statement, including one with a built-in aggregate function, such as count or sum, that returns a single value.
-- set the default database context, such as for Database_name
use Database_name
go
-- you can conditionally drop a scalar-valued function with this script
-- schema_name denotes the schema in which the udf is created
-- udf_name is the name of the function
IF object_id (N'[schema_name].[udf_name]', N'FN') is not null
drop function [schema_name].[udf_name]
go
-- create a scalar-valued function with a create function statement
-- after create function specify a two-part name: schema_name.udf_name
-- an optional parameter list can appear in parentheses after the two-part name
-- a returns clause specifies a datatype for the returned scalar value; this instance of
-- the template
-- as keyword separates udf declarations
-- from udf code for return value in a begin...end block
-- include udf code in parentheses following return keyword
-- code can be a T-SQL expression or a select statement for an aggregate value
-- use go keyword to invoke the create function statement
create function schema_name.udf_name
(@param1 int, @param2 int)
returns int
as
begin
return (T-SQL expression or a select statement for a single value)
end
go
The next code block shows two different ways of applying a scalar-valued UDF after you create it. Again, the code is meant to be used as a template.
- The first select statement shows how to use a scalar-valued UDF as a list item in a SELECT statement.
- The remainder of script demonstrates how to use an EXEC statement to populate a local variable with the return value from a scalar-valued UDF. After the return value is copied to a local variable, it is displayed in the final SELECT statement with an alias of "from exec" for the local variable value.
As demonstrated in the template below, a scalar-value does not need to be qualified by schema name when it passes a value to a local variable via an EXEC statement. SQL Docs confirms this assertion though it does not present a working example (however, I verified the feature in my own personal tests). When the scalar-valued UDF does not have a schema name qualifier, the user-defined module can be derived from the default schema for the user.
-- you can reference a scalar-valued udf (udf_name) from a select statement to display its value -- value_1 and value_2 are parameters for udf_name -- the udf can appear by itself as a list item or along with other list items -- optionally, designate an item name alias, such as 'from select' select schema_name.udf_name(value_1,value_2) [from select] -- you can also display a scalar value from a udf with an exec statement -- exec statement invocation does not require a schema name qualifier for the udf -- but the statement does require a local variable to accept the returned value -- after the exec statement populates the local variable -- display the local variable value in a select statement or use it in some other way declare @my_local_variable real exec @my_local_variable = udf_name value_1,value_2 select @my_local_variable [from exec]
Syntax conventions for inline table-valued functions
Here’s another script template. This one is to create a fresh copy of an inline table-valued UDF. It is assumed the code runs in an appropriate database context, such as any user-defined database. Invoke a use statement to specify the database name before running the code in the template below.
- A try…catch block is used to drop any prior version of the UDF named udf_inline_name, which is created in the schema_name schema. The catch block displays the SQL Server error number and message, if the DROP FUNCTION statement fails for any reason. You can use the error number and message to help identify and resolve the source of the error.
- Again, a create function statement is used to create a fresh copy of the
UDF. Notice there are subtle differences between the statement when used
for an inline table-valued UDF instead of a scalar-valued UDF.
- The returns keyword must specify a table type for the returned rowset.
- Additionally, there is a single SELECT statement embedded as an argument in the return keyword. The SELECT statement is to return one or more rows. This tip demonstrates the use of an optional where clause criterion that is based on the @param1 input parameter.
- A SELECT statement after the go keyword for the create function statement displays the values returned by the inline table-valued UDF. The value in parentheses after the SELECT statement are for optional parameter values. Even if an inline UDF does not require any input parameters, you still need the trailing parentheses.
-- you can conditionally drop udfs, such as inline table-valued user-defined functions,-- with try...catch blocks
-- the same syntax can also work for dropping a scalar-valued udf
begin try
drop function [schema_name].[udf_inline_name]
end try
begin catch
select
error_number() [Error Number]
,error_message() [Error Message]
end catch
go
-- create an inline table-value function with a create function statement
-- after create function specify a two-part name: schema_name.udf_inline_name
-- an optional parameter list can appear in parentheses after the two-part name
-- a returns clause specifies a table data type for the function's return value
-- the table will hold the return rowset
-- as keyword separates udf declarations
-- from udf code for the return value in a return clause
-- inside the parentheses for the return clause is a select statement
-- with an optional where clause for the parameter set to specify
-- which rows to return from the data sources
-- use go keyword to launch the create function statement
-- before invoking the function in a trailing select statement within the same script
create function schema_name.udf_inline_name
(@param1 int)
returns table
as
return
(
SELECT item list
from data_source(s)
where data_source_field_name = @param1
)
go
-------------------------------------------------------------------------------------------
-- you can reference an inline table-valued udf in
-- the from clause of a select statement to display the return rowset
-- specify any parameters for the udf in parentheses after the two-part udf name
select * from schema_name.udf_inline_name(value_1)
Syntax conventions for multi-statement table-valued functions
Here’s another script template. This one illustrates how to create a fresh copy of a multi-statement table-valued UDF as well as access to the data from the UDF in the FROM clause of a SELECT statement. It is assumed the code runs in an appropriate database context, such as any user-defined database. There are three parts to the script.
- First, the code drops an old version of the udf_multi_statement_name UDF if one exists already.
- The go at the end of the try…catch lets the following create function be the first statement in a batch, which is required.
- Next, the code invokes a create function statement to create a fresh copy
of the multi-statement table-valued UDF.
- This function does not require any input parameters, but you must still end the initial create function line with trailing empty parentheses.
- The returns clause specifies the column names and types for the table variable returned from the UDF. Adjust the number of columns and the column names based on your requirements.
- The begin…end block in this tip example populates the returned table variable via two different insert into statements from multiple sources (data_source_1 and data_source_2).
- The GO statement after the create function and before the trailing select statement instructs SQL Server to run the create function before attempting to run the SELECT statement that depends on the function. This GO statement would not be necessary if the CREATE FUNCTION statement and its trailing SELECT statement were in separate scripts.
- Lastly, a SELECT statement displays values from the udf_multi_statement_name udf.
-- you can conditionally drop multi-statement table-valued user-defined functions from -- within try...catch blocks
begin try
drop function schema_name.udf_multi_statement_name
end try
begin catch
select
error_number() [Error Number]
,error_message() [Error Message]
end catch
go
create function schema_name.udf_multi_statement_name()
-- you can place optional parameter list in the parentheses at the end of the create function line
returns @output_table_name table
(
[column1_name] varchar(max),
[column2_name] varchar(max),
[column3_name] varchar(max),
[column4_name] varchar(max)
)
as
begin
-- specify first insert into @output_table_name
insert into @output_table_name
-- select statement can include a where clause for parameters if they are declared
select item list
from data_source_1;
-- specify as many additional inserts into @output_table_name
-- as your udf requires; you can use other statements
-- in the begin...end block, such as control flow statements
insert into @output_table_name
-- select statement can include a where clause for parameters if they are declared
select item list
from data_source_2;
-- returns control from the multi-statement table-value udf
return;
end;
go
-- you can reference a multi-statement table-valued udf in
-- the from clause of a select statement to display the return rowset
-- specify any parameters for the udf in parentheses after the two-part udf name
-- in the example, there are no optional parameters
-- select from multi-statement udf
select * from schema_name.udf_multi_statement_name()
Next Steps
- The code samples from this tip are pseudo code in templates. To run the code, you need to replace key parts with real code. You can use any user-defined database and schema within it that you want as a repository for the updated templates based on your requirements.
- Another tip will appear soon with specific examples of actual user-defined defined functions as opposed to the templates shown in this tip. The subsequent tip will also address some computational issues that you may find useful as you apply user-defined functions.
- Check out all of the SQL Server User Defined Functions tips.
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-10-08
