Creating a Function

Although functions are used to perform calculations, a function can’t change the state of a database or instance. Functions can’t:

  • Perform an action that changes the state of an instance or database.

  • Modify data in a table.

  • Call a function that has an external effect such as the RAND() function.

  • Create or access temporary tables.

  • Dynamically execute code.

Functions can either return a scalar-value or a table. Table-valued functions can be of two different types: inline and multi-statement.

The general syntax for a scalar function is:

CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] } [ ,...n ] ]) RETURNS return_data_type [ WITH <function_option> ...

Get Microsoft® SQL Server® 2008 Step by Step now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.