SQL Functions

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.

Example

#

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
Result:  91 records
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

Using User-defined Functions

#

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

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

Built-in Functions

SQL Server has many built-in functions.
Below are some commonly used ones.

Scalar Functions

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

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

You may also like



Guides


vsn 3.1