What is User-Defined Function (UDF) in SQL Server?

User-Defined Functions allow to define its own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type. A function can have a maximum of 1024 input parameters. When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value. This behavior is different from parameters with default values in user-defined stored procedures in which omitting the parameter also implies the default value. User-defined functions do not support output parameters. When you can't find a built-in function that meets your needs, you can write your own.

User-defined functions are created using the CREATE FUNCTION statement, modified using the ALTER FUNCTION statement, and removed using the DROP FUNCTION statement. Each fully qualified user-defined function name (schema_name.function_name) must be unique.


Valid Statements in a Function
The types of statements that are valid in a function include:

• DECLARE statements can be used to define data variables and cursors that are local to the function.
• Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.
• Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.
• Control-of-flow statements except TRY...CATCH statements.
• SELECT statements containing select lists with expressions that assign values to variables that are local to the function.
• UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.
• EXECUTE statements calling an extended stored procedure.

 The following nondeterministic built-in functions cannot be used in Transact-SQL user-defined functions.

1. NEWID
2. RAND
3. NEWSEQUENTIALID
4. TEXTPTR