CREATE FUNCTION#
Creates a user-defined function (UDF). A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, and returns the result of that action.
Syntax#
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] }
[ ,...n ]
)
RETURNS TABLE <clr_table_type_definition>
[ AS ] EXTERNAL NAME <method_specifier>
[ ; ]
<clr_table_type_definition>::=
( { column_name data_type } [ ,...n ] )
<method_specifier>::=
assembly_name.class_name.method_name
Arguments#
OR ALTER
Conditionally alters the function if it already exists.
schema_name
Name of the schema to which the user-defined function belongs.
function_name
Name of the user-defined function. Function names must comply with the rules for identifiers and must be unique within the database and to its schema.
@parameter_name
A parameter in the user-defined function. One or more parameters can be declared. Specify a parameter name by using an at sign (@) as the first character. The parameter name must comply with the rules for identifiers. Parameters are local to the function.
The value of each declared parameter must be supplied by the user when the function is executed, unless a default for the parameter is defined.
[ type_schema_name. ] parameter_data_type
Parameter data type, and optionally the schema to which it belongs.
All data types are allowed except text, ntext, image, user-defined table types and timestamp data types.
[ =default ]
A default value for the parameter. If a default value is defined, the function can be executed without specifying a value for that parameter, provided that the keyword DEFAULT is specified.
TABLE
Specifies that the return value of the table-valued function (TVF) is a table.
EXTERNAL NAME <method_specifier> assembly_name.class_name.method_name
Specifies the assembly and method to which the created function name shall refer.
assembly_name - must match a value in the name column from sys.assemblies.
class_name - must match a value in the assembly_name column from sys.assembly_modules. Often the value contains an embedded period or dot. In such cases the Transact-SQL syntax requires that the value be bounded with a pair of straight brackets [], or with a pair of double quotation marks “”.
method_name - The name of the REST operation defined in OpenApi definition.
Note
For now, the only supported EXTERNAL NAME value is ‘[BuiltIn].[Rest]’ with a postfix of the REST operation to run, for example, ‘[BuiltIn].[Rest].[REST_operation_name]’.
<clr_table_type_definition> ( { column_name**data_type } [ ,…n ] )
Defines the table data types for a CLR function. The table declaration includes only column names and data types.
Column name must be equal to the path of the element to return from the JSON response resulting from the REST call.
Example#
Consider the snippet from the Swagger’s PetStore OpenApi specification, available at Petstore, defining a single REST operation with id findPetsByStatus:
"/pet/findByStatus": {
"get": {
"summary": "Finds Pets by status",
"description": "Multiple status values can be provided with comma separated strings",
"operationId": "findPetsByStatus",
"produces": [ "application/json", "application/xml" ],
"parameters": [
{
"name": "status",
"in": "query",
"description": "Status values that need to be considered for filter",
"required": true,
"type": "array",
"items": {
"type": "string",
"enum": [ "available", "pending", "sold" ],
"default": "available"
},
"collectionFormat": "multi"
}
],
"responses": {
"200": {
"description": "successful operation",
"schema": {
"type": "array",
"items": { "$ref": "#/definitions/Pet" }
}
},
"400": { "description": "Invalid status value" }
},
"security": [ { "petstore_auth": [ "write:pets", "read:pets" ] } ]
}
},
...
"definitions": {
"Category": {
"type": "object",
"properties": {
"id": {
"type": "integer",
"format": "int64"
},
"name": { "type": "string" }
},
"xml": { "name": "Category" }
},
"Pet": {
"type": "object",
"required": [ "name", "photoUrls" ],
"properties": {
"id": {
"type": "integer",
"format": "int64"
},
"category": { "$ref": "#/definitions/Category" },
"name": {
"type": "string",
"example": "doggie"
},
"photoUrls": {
"type": "array",
"xml": { "wrapped": true },
"items": {
"type": "string",
"xml": { "name": "photoUrl" }
}
},
"tags": {
"type": "array",
"xml": { "wrapped": true },
"items": {
"xml": { "name": "tag" },
"$ref": "#/definitions/Tag"
}
},
"status": {
"type": "string",
"description": "pet status in the store",
"enum": [ "available", "pending", "sold" ]
}
},
"xml": { "name": "Pet" }
}
(...)
The corresponding UDF that wrapps the above operation and makes it callable from SQL is:
CREATE OR ALTER FUNCTION [findPetsByStatus]
(
@status nvarchar(200)
)
RETURNS TABLE
(
[id] bigint,
[category.id] bigint,
[category.name] nvarchar(2000),
[name] nvarchar(2000),
[photoUrls] nvarchar(2000),
[tags.id] bigint,
[tags.name] nvarchar(2000),
[status] nvarchar(2000)
)
EXTERNAL NAME [BuiltIn].[Rest].[findPetsByStatus];
Pet object definition includes the Tag and Category. Both of them are compound objects, so to navigate to their values, we need to use a path expression like category.id or tag.name.
To map column to path expression we encompass the path full expression in the column name.
To use the function in SQL, use the following query, that returns all Pets with the status ‘sold’:
SELECT *
FROM [petstore].[dbo].[findPetsByStatus]('sold')
Example result:
id category.id category.name name photoUrls tags.id tags.name status
----- ----------- ------------- ------ --------------------------------- ------- --------- ------
1545 1 Dogs Roger https://petstore.swagger.io/p.png 1 lovely sold
7920 1 Dogs Mojito https://petstore.swagger.io/a.png 0 woof sold
4774 2 Puppy Katy https://petstore.swagger.io/b.png 0 woof sold
2045 2 Puppy Limka https://petstore.swagger.io/c.png 0 woof sold
9999 2 Puppy Pepka https://petstore.swagger.io/d.png 0 woof sold