Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
UDF Syntax
All UDFs are defined using the CREATE FUNCTION statement, which varies slightly for scalar, in-line, and multi-statement functions.
Scalar UDFs have the following syntax:
CREATE FUNCTION owner.function_name
(parameter_name data_type [=default] [, n])
RETURNS scalar_data_type [WITH function_option] AS BEGIN Function_body RETURN scalar_expression END
In-line UDFs have the following syntax:
CREATE FUNCTION owner.function_name (parameter_name data_type [=default] [, n]) RETURNS TABLE [WITH function_option] AS RETURN ( SELECT_statement )
Multi-statement UDFs have the following syntax:
CREATE FUNCTION owner.function_name
((parameter_name data_type [=default] [, n])
RETURNS @table_variable_name TABLE (table_definition)
[WITH function_option]
AS BEGIN
Function_body
RETURN
END
As the syntax shows, in-line and multi-statement functions are very similar. The difference is that in-line function doesn't have to define the table variable that will be returned, whereas the multi-statement function has to define the table structure at the beginning. Functions can accept multiple input parameters (up to 1024), and you can specify a default value for each parameter. Functions do not support output parameters.
The Function option can be ENCRYPTION and/or SCHEMABINDING. The ENCRYPTION option advises SQL Server to encrypt the code of the UDF, whereas SCHEMABINDING disallows changes to the underlying database objects.
Personally, I'm not a big proponent of the WITH ENCRYPTION option, whether used with a UDF or another reusable database routine. Keep in mind that once you've encrypted the function, there is no easy way to get the code back. Some companies selling installed software encrypt their code in hopes that their customers can't customize and/or troubleshoot their product. Be aware that there might be some third-party utilities that can decrypt your code.
The WITH SCHEMABINDING option can be useful if you have multiple team members making changes to the database objects. If you create a function using the SCHEMABINDING keyword, other team members get an error if they attempt to modify objects referenced by your function. Realize, however, that once you bind the function definition to the schema, you lose the ability to easily change the underlying objects. So if your database schema is evolving, wait until you get to the stable phase prior to using SCHEMABINDING option.
UDFs can be modified using the ALTER FUNCTION statement. The syntax of this statement is identical to CREATE FUNCTION, with the exception of using the ALTER keyword instead of CREATE.
If you no longer need a particular UDF, it can be deleted by using the DROP FUNCTION statement. The DROP FUNCTION syntax requires only the function owner and name, as in the following:
DROP FUNCTION dbo.my_outdated_function