Section courante

A propos

Section administrative du site

CREATE FUNCTION

 Crée une fonction
  Microsoft SQL Server

Syntaxe

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
[ ; ]
 Syntaxe de la fonction scalaire Transact-SQL
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 [ ) ]
[ ; ]
 Syntaxe de la fonction de valeur de table en ligne Transact-SQL
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
[ ; ]
 Syntaxe de la fonction de valeur table Transact-SQL à multiple instruction
function_option::=
{
[ ENCRYPTION ]
| [ SCHEMABINDING ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
| [ INLINE = { ON | OFF }]
}

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 }
}
 Syntaxe de clauses de fonction Transact-SQL
CREATE FUNCTION [ schema_name. ] function_name
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
)
RETURNS { return_data_type }
[ WITH clr_function_option [ ,...n ] ]
[ AS ] EXTERNAL NAME method_specifier
[ ; ]
 Syntaxe de la fonction scalaire CLR
CREATE FUNCTION [ schema_name. ] function_name
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
)
RETURNS TABLE clr_table_type_definition
[ WITH clr_function_option [ ,...n ] ]
[ ORDER ( order_clause ) ]
[ AS ] EXTERNAL NAME method_specifier
[ ; ]
 Syntaxe de la fonction table CLR
order_clause ::=
{
column_name_in_clr_table_type_definition
[ ASC | DESC ]
} [ ,...n]

method_specifier::=
assembly_name.class_name.method_name

clr_function_option::=
}
[ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
}

clr_table_type_definition::=
( { column_name data_type } [ ,...n ] )
 Syntaxe de clauses de fonction CLR
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ NULL | NOT NULL ] [ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS return_data_type
WITH function_option [ ,...n ]
[ AS ]
BEGIN ATOMIC WITH (set_option [ ,... n ])
function_body
RETURN scalar_expression
END

function_option::=
{
| NATIVE_COMPILATION
| SCHEMABINDING
| [ EXECUTE_AS_Clause ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}
 Syntaxe de la fonction scalaire compilée en mode natif (OLTP en mémoire)

Description

Cette instruction permet de créer une fonction utilisateur dans une base de données SQL Server.



Dernière mise à jour : Vendredi, le 19 Juin 2020