A SQL function is a set of statements that perform a specific task.
Functions fall into 2 categories: user-defined and built-in.
To create a user-defined function use the CREATE FUNCTION command.
This example creates a function that joins firstname and lastname.
CREATE FUNCTION FullName (
@FirstName NVARCHAR(40),
@LastName NVARCHAR(40)
)
RETURNS NVARCHAR(81) AS
BEGIN
RETURN (SELECT @FirstName + ' ' + @LastName)
END;
GO
Once created, the function can be used like any other built-in function, like so:
SELECT dbo.FullName(FirstName, LastName) AS Name,
City, Country
FROM Customer
| Name | City | Country |
|---|---|---|
| Maria Anders | Berlin | Germany |
| Ana Trujillo | México D.F. | Mexico |
| Antonio Moreno | México D.F. | Mexico |
| Thomas Hardy | London | UK |
| Christina Berglund | Luleå | Sweden |
 |
||
A user-defined function must have a name.
The name cannot start with a special character like @, $, #, and others.
Functions can accept any number of input parameters.
Functions compile every time they are used.
Functions must always return a value or result.
Try-catch cannot be used in functions.
Syntax to create a scalar function.
CREATE FUNCTION function_name ( @parameter1 AS data_type, @parameter2 AS data_type, ) RETURNS return_data_type BEGIN function_body RETURN scalar_value END
Note: Scalar functions return a single value.
Syntax to change a scalar function.
ALTER FUNCTION function_name ( @parameter1 AS data_type, @parameter2 AS data_type, ) RETURNS return_data_type BEGIN function_body RETURN scalar_value END
Syntax to remove a function.
DROP FUNCTION function_name
SQL Server has many built-in functions.
Below are some commonly used ones.
Scalar functions return a single value based on input value.
| Function | Description |
|---|---|
| LOWER | Converts a string to lowercase |
| TRIM | Removes space characters from left and right side of string |
| LEN | Returns length of a string |
| SUBSTRING | Returns a substring from a string |
| CHARINDEX | Returns starting position of character expression in a string |
| ROUND | Rounds numeric value to the number of decimals specified |
| SQRT | Returns the square root of the specified float value |
| SIN | Returns the trigonometric sine of the specified angle |
| GETDATE | Returns current system date and time |
| YEAR | Returns an integer that represents the year of a specified date |
| IIF | Returns one of two input values depending on a boolean expression |
Aggregate functions return a single value calculated from column values.
| Function | Description |
|---|---|
| AVG | Returns the average value |
| COUNT | Returns a count of rows |
| MIN | Returns minimum value |
| MAX | Returns maximum value |
| SUM | Returns a sum value |
| STDEV | Returns a standard deviation value |
