SQL Server: Functions

Learn how to create and drop functions in SQL Server (Transact-SQL) with syntax and examples.

What is a function in SQL Server?

In SQL Server, a function is a stored program that you can pass parameters into and return a value.

Create Function

You can create your own functions in SQL Server (Transact-SQL). Let's take a closer look.

Syntax

The syntax to create a function in SQL Server (Transact-SQL) is:

CREATE FUNCTION [schema_name.]function_name
( [ @parameter [ AS ] [type_schema_name.] datatype 
    [ = default ] [ READONLY ]
  , @parameter [ AS ] [type_schema_name.] datatype 
    [ = default ] [ READONLY ] ]
)

RETURNS return_datatype

[ WITH { ENCRYPTION
       | SCHEMABINDING
       | RETURNS NULL ON NULL INPUT
       | CALLED ON NULL INPUT
       | EXECUTE AS Clause ]

[ AS ]

BEGIN

   [declaration_section]

   executable_section

   RETURN return_value

END;

schema_name: The name of the schema that owns the function.
function_name: The name to assign to this function in SQL Server.
@parameter: One or more parameters passed into the function.
type_schema_name: The schema that owns the data type, if applicable.
datatype: The data type for @parameter.
default: The default value to assign to @parameter.
READONLY: It means that @parameter can not be overwritten by the function.
return_datatype: The datatype of the function's return value.
ENCRYPTION: It means that the source for the function will not be stored as plain text in the system views in SQL Server.
SCHEMABINDING: It means that the underlying objects can not be modified so as to affect the function.
RETURNS NULL ON NULL INPUT: It means that the function will return NULL if any parameters are NULL without having to execute the function.
CALL ON NULL INPUT: It means that the function will execute the function even if any parameters are NULL.
EXECUTE AS clause: Sets the security context to execute the function.
return_value: The value returned by the function.

Example

Let's look at an example of how to create a function in SQL Server (Transact-SQL).

The following is a simple example of a function:

CREATE FUNCTION ReturnSite
( @site_id INT )

RETURNS VARCHAR(50)

AS

BEGIN

   DECLARE @site_name VARCHAR(50);

   IF @site_id < 10
      SET @site_name = 'TechOnTheNet.com';
   ELSE
      SET @site_name = 'CheckYourMath.com';

   RETURN @site_name;

END;

This function is called ReturnSite. It has one parameter called @site_id which is an INT datatype. The function returns a VARCHAR(50) value, as specified by the RETURNS clause.

You could then reference the new function called ReturnSite as follows:

USE [test]
GO

SELECT dbo.ReturnSite(8);

GO

Drop Function

Once you have created your function in SQL Server (Transact-SQL), you might find that you need to remove it from the database.

Syntax

The syntax to a drop a function in SQL Server (Transact-SQL) is:

DROP FUNCTION function_name;

function_name: The name of the function that you wish to drop.

Example

Let's look at an example of how to drop a function in SQL Server.

For example:

DROP FUNCTION ReturnSite;

This DROP FUNCTION example would drop the function called ReturnSite.

SQL Server Tutorial