Export (0) Print
Expand All

CREATE FUNCTION (Azure SQL Database)

This topic is OBSOLETE. You can find the most current version in the SQL 14 Transact-SQL Reference.

ImportantImportant
This topic is not maintained. For the current version, see CREATE FUNCTION (Transact-SQL).

Creates a user-defined function. This is a saved Transact-SQL routine that returns a value. User-defined functions cannot be used to perform actions that modify the database state. User-defined functions, like system functions, can be invoked from a query. Scalar functions can be executed by using an EXECUTE statement, as with stored procedures. 

Syntax Conventions (Azure SQL Database)

Scalar Functions
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> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

Inline Table-Valued Functions
CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ =default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Multistatement Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ =default ] [READONLY] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END
[ ; ]



Function Options<function_option>::= 
{
    [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}


Table Type Definitions<table_type_definition>:: = ( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
    [ <table_constraint> ] [ ,...n ]
)<column_definition>::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ] 
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ] 
}
<column_constraint>::= 
{
    [ NULL | NOT NULL ] 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
        [ WITH FILLFACTOR =fillfactor 
        | WITH ( < index_option > [ , ...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK (logical_expression ) ] [ ,...n ]
}

<computed_column_definition>::=column_name AS computed_column_expression<table_constraint>::=
{ 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
      (column_name [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK (logical_expression ) ] [ ,...n ]
}

<index_option>::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS ={ ON | OFF } 
}

This syntax diagram demonstrates the supported arguments and function types in Microsoft Microsoft Azure SQL Database.

Microsoft Azure SQL Database does not support creating CLR scalar functions and CLR table-valued functions by using the CREATE FUNCTION statement. Microsoft Azure SQL Database does support creating scalar functions, inline table-valued functions, and multistatement table-valued functions without any modification.

For scalar functions and multistatement table-valued functions, function_body is subject to the general Microsoft Azure SQL Database Transact-SQL data manipulation language (DML) limitations. The following list shows the DDLs, which are subject to the general Microsoft Azure SQL Database Transact-SQL data definition language (DDL) limitations:

  • table_type_definition

  • column_definition

  • column_constraint

  • computed_column_definition

  • index_option

For more information about the arguments and the CREATE FUNCTION statement, see CREATE FUNCTION (Transact-SQL) in SQL Server Books Online.

Community Additions

ADD
Show:
© 2014 Microsoft