User-Defined function (UDF) was introduced in SQL Server 2000. UDF can be used in a complex T-SQL Query, and will be used in problems that were impossible or required cursors can now be solved with UDFs.
- UDFs can be used in complex logic within a query
- UDFs can be used to create new functions for complex expressions.
- UDFs offers the benefits of views because they can be used in the “from clause” of the select statement. User defined functions accepts parameters whereas views cannot.
- UDFs offer the benefits of stored procedures because they are compiled and optimized in the same way.
User-Defined functions come in three types
- Scalar functions that return a single value
- Inline functions similar to views
- Multi-statement table functions
A scalar function will return a single specific value. The function can accept multiple parameters and perform calculations then return a single value. These UDFs can be used within any expressions. By using the return command we can return the value. The return command should be the last command in the user-defined function.
User-Defined scalar functions are not permitted to update the databases, but they can access the temporary tables.
They cannot return Binary Large Object (BLOB) data like ntext, text and image data type variables.
Creating a Scalar Function
User Defined Functions can be created, altered and dropped by using the DDL commands.
- CREATEFUNCTION FunctionName (Input Parameters)
- RETURN DataType
- RETURN Expression
We will specify the data-type definition with the Input Parameters. Optionally can include the default value like stored procedure parameters.
The following user-defined function performs a simple mathematical function. The second parameter includes a default value:
- CREATEFUNCTION ADDITION(@A INT,@B INT = 1)
- RETURN @A + @B
In the above UDF ADDITION defined with two parameters A and B and will return the sum of the two input values. The second parameter B is optional. If no value received then the value 1 will be assigned in the parameter @B. The next line to the Create statement “returns int” means will return integer value.
Use the following query to call the function
- SELECT DBO.ADDITION(3,2)
It will return 5. (@A=3 and @B=2)
- SELECT DBO.ADDITION(3,DEFAULT)
It will return 4 (@A=3 and @B=1)
In the function, second parameter is Optional. In UDF calling have to specify DEFALT for optional parameters. But in the stored procedure we will not specify anything.
Inline Table-Valued Functions
A inline Table-Valued function returns data type of table which is derived from a single SELECT statement. We no need to use begin and end statement because return values is derived from the SELECT statement.
CREATE FUNCTION FunctionName (InputParamters)
RETURN (Select Statement)
- CREATEFUNCTION fx_CompanyByCity
- ( @City nvarchar(100) )
- RETURN (
- SELECT *
- FROM Company
- WHERE City =@City