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 = 'AODBA.com';
   ELSE
      SET @site_name = 'mySite.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 <strong>function_name</strong>;

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.