CREATE FUNCTION (Transact-SQL)
Gilt für: SQL Server
Azure SQL-Datenbank
Azure SQL verwaltete Instanz
Erstellt eine benutzerdefinierte Funktion (UDF), bei der es sich um eine Transact-SQL- oder Common Language Runtime (CLR)-Routine handelt. Eine benutzerdefinierte Funktion akzeptiert Parameter, führt eine Aktion wie eine komplexe Berechnung aus und gibt das Ergebnis dieser Aktion als Wert zurück. Der Rückgabewert kann ein Skalarwert (Einzelwert) oder eine Tabelle sein. Verwenden Sie diese Anweisung zum Erstellen einer wiederverwendbaren Routine, die auf folgende Weise verwendet werden kann:
- In Transact-SQL-Anweisungen, z. B.
SELECT
- In Anwendungen, die die Funktion aufrufen
- Bei der Definition einer anderen benutzerdefinierten Funktion
- Zum Parametrisieren einer Sicht oder zur Verbesserung der Funktionalität einer indizierten Sicht
- Zum Definieren einer Spalte in einer Tabelle
- So definieren Sie eine
CHECK
Einschränkung für eine Spalte - Zum Ersetzen einer gespeicherten Prozedur
- Zum Verwenden einer Inlinefunktion als Filterprädikat für eine Sicherheitsrichtlinie
Die Integration der .NET Framework-CLR in SQL Server wird in diesem Artikel erläutert. Die CLR-Integration gilt nicht für Azure SQL-Datenbank.
Informationen zu Azure Synapse Analytics oder Microsoft Fabric finden Sie unter CREATE FUNCTION (Azure Synapse Analytics und Microsoft Fabric).
Transact-SQL-Syntaxkonventionen
Syntax
Syntax für Transact-SQL-Skalarfunktionen.
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] [ READONLY ] }
[ , ...n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ , ...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]
Syntax für Transact-SQL-Inlinetabellenwertfunktionen.
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] [ READONLY ] }
[ , ...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ , ...n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]
Syntax für Transact-SQL multi-statement table-valued functions.
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] [ READONLY ] }
[ , ...n ]
]
)
RETURNS @return_variable TABLE <table_type_definition>
[ WITH <function_option> [ , ...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
[ ; ]
Syntax für Transact-SQL-Funktionsklauseln.
<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 }
}
Syntax für CLR-Skalarfunktionen.
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] }
[ , ...n ]
)
RETURNS { return_data_type }
[ WITH <clr_function_option> [ , ...n ] ]
[ AS ] EXTERNAL NAME <method_specifier>
[ ; ]
Syntax für CLR-Tabellenwertfunktionen.
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>
[ WITH <clr_function_option> [ , ...n ] ]
[ ORDER ( <order_clause> ) ]
[ AS ] EXTERNAL NAME <method_specifier>
[ ; ]
Syntax für CLR-Funktionsklauseln.
<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 ] )
In-Memory OLTP-Syntax für systemeigene kompilierte, skalare benutzerdefinierte Funktionen.
CREATE [ OR ALTER ] 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 ]
}
Hinweis
Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 (12.x) und früher finden Sie unter Dokumentation zu früheren Versionen.
Argumente
OR ALTER
Gilt für: SQL Server 2016 (13.x) SP 1 und höhere Versionen und Azure SQL-Datenbank.
Ändert die Funktion bedingt, sofern diese bereits vorhanden ist.
Optionale OR ALTER
Syntax ist für CLR verfügbar, beginnend mit SQL Server 2016 (13.x) SP 1 CU 1.
schema_name
Der Name des Schemas, zu dem die benutzerdefinierte Funktion gehört.
function_name
Der Name der benutzerdefinierten Funktion. Funktionsnamen müssen den Regeln für Bezeichner entsprechen und innerhalb der Datenbank und für jedes Schema eindeutig sein.
Klammern sind nach dem Funktionsnamen erforderlich, auch wenn kein Parameter angegeben ist.
@parameter_name
Ein Parameter in der benutzerdefinierten Funktion. Ein oder mehrere Parameter können deklariert werden.
Eine Funktion kann maximal 2.100 Parameter haben. Der Benutzer muss beim Ausführen einer Funktion den Wert jedes deklarierten Parameters angeben (sofern kein Standardwert für den betreffenden Parameter definiert ist).
Geben Sie einen Parameternamen an, der mit dem Zeichen (@) beginnt. Der Parametername muss den Regeln für Bezeichner entsprechen. Parameter gelten lokal in der jeweiligen Funktion. Dieselben Parameternamen können in anderen Funktionen verwendet werden. Parameter können nur von Konstanten verwendet werden; sie können nicht anstelle von Tabellennamen, Spaltennamen oder den Namen anderer Datenbankobjekte verwendet werden.
ANSI_WARNINGS
wird nicht berücksichtigt, wenn Sie Parameter in einer gespeicherten Prozedur, benutzerdefinierten Funktion oder beim Deklarieren und Festlegen von Variablen in einer Batch-Anweisung übergeben. Wird beispielsweise eine Variable als char(3) definiert und dann auf einen Wert festgelegt, der länger als drei Zeichen ist, werden die Daten auf die definierte Größe abgeschnitten, und die Anweisung INSERT
oder UPDATE
wird erfolgreich ausgeführt.
[ type_schema_name. ] parameter_data_type
Der Parameterdatentyp und optional das Schema, zu dem er gehört. Für Transact-SQL-Funktionen sind abgesehen vom timestamp-Datentyp alle Datentypen zulässig, einschließlich CLR-benutzerdefinierter Typen und benutzerdefinierter Tabellentypen. Für CLR-Funktionen sind alle Datentypen, einschließlich benutzerdefinierter CLR-Typen, mit Ausnahme von Text, ntext, Bild, benutzerdefinierten Tabellentypen und Zeitstempel-Datentypen zulässig. Die nichtkalaren Typen, Cursor und Tabellen können nicht als Parameterdatentyp in Transact-SQL- oder CLR-Funktionen angegeben werden.
Wenn type_schema_name nicht angegeben ist, sucht die Datenbank-Engine in der folgenden Reihenfolge nach dem scalar_parameter_data_type:
- Das Schema, das die Namen von SQL Server-Systemdatentypen enthält
- Das Standardschema des aktuellen Benutzers in der aktuellen Datenbank
- Das dbo -Schema in der aktuellen Datenbank
[ = Standard ]
Ein Standardwert für den Parameter. Wenn ein default-Wert definiert ist, kann die Funktion ausgeführt werden, ohne dass ein Wert für diesen Parameter angegeben werden muss.
Standardwerte können für CLR-Funktionen angegeben werden, mit Ausnahme der Datentypen varchar(max) und varbinary(max).
Wenn ein Parameter der Funktion einen Standardwert aufweist, muss das Schlüsselwort DEFAULT
angegeben werden, wenn die Funktion aufgerufen wird, um den Standardwert abzurufen. In diesem Punkt gibt es einen Unterschied zum Verwenden von Parametern in einer gespeicherten Prozedur. Fehlt im Aufruf einer gespeicherten Prozedur ein Parameter, der einen Standardwert hat, wird automatisch dieser Standardwert verwendet. Das DEFAULT
Schlüsselwort ist jedoch beim Aufrufen einer skalaren Funktion mithilfe der EXECUTE
Anweisung nicht erforderlich.
READONLY
Gibt an, dass der Parameter nicht innerhalb der Definition der Funktion aktualisiert oder geändert werden kann. READONLY
ist für benutzerdefinierte Tabellentypparameter (TVPs) erforderlich und kann nicht für einen anderen Parametertyp verwendet werden.
return_data_type
Der Rückgabewert einer skalaren benutzerdefinierten Funktion. Für Transact-SQL-Funktionen sind abgesehen vom timestamp-Datentyp alle Datentypen zulässig, einschließlich benutzerdefinierter CLR-Typen. Für CLR-Funktionen sind abgesehen von den Datentypen text, ntext, image und timestamp alle Datentypen zulässig, einschließlich benutzerdefinierter CLR-Typen. Die nichtkalaren Typen, Cursor und Tabellen können nicht als Rückgabedatentyp in Transact-SQL- oder CLR-Funktionen angegeben werden.
function_body
Gibt an, dass eine Reihe von Transact-SQL-Anweisungen, die zusammen keinen Nebeneffekt erzeugen, z. B. das Ändern einer Tabelle, den Wert der Funktion definieren. function_body wird nur in Skalarfunktionen sowie in Tabellenwertfunktionen mit mehreren Anweisungen verwendet.
In Skalarfunktionen entspricht function_body einer Reihe von Transact-SQL-Anweisungen, die zusammen einen Skalarwert ergeben.
In MSTVFs ist function_body eine Reihe von Transact-SQL-Anweisungen, die eine TABLE
Rückgabevariable auffüllen.
scalar_expression
Gibt den skalaren Wert an, den die Skalarfunktion zurückgibt.
TABLE
Gibt an, dass der Rückgabewert der Tabellenwertfunktion eine Tabelle ist. Nur Konstanten und @local_variables können an Tabellenwertfunktionen übergeben werden.
Inline-TVFs wird der TABLE
Rückgabewert über eine einzelne SELECT
Anweisung definiert. Inlinefunktionen verfügen nicht über zugeordnete Rückgabevariablen.
In MSTVFs ist @return_variable eine TABLE
Variable, die zum Speichern und Ansammeln der Zeilen verwendet wird, die als Wert der Funktion zurückgegeben werden sollen. @return_variable kann nur für Transact-SQL-Funktionen, nicht für CLR-Funktionen angegeben werden.
select_stmt
Die einzelne SELECT
Anweisung, die den Rückgabewert einer inlinetabellenwertigen Funktion (TVF) definiert.
ORDER (<order_clause>)
Gibt die Reihenfolge an, in der Ergebnisse von der Tabellenwertfunktion zurückgegeben werden. Weitere Informationen finden Sie im Abschnitt " Verwenden der Sortierreihenfolge in CLR-Tabellenwertfunktionen " weiter unten in diesem Artikel.
EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name
Gilt für: SQL Server 2008 (10.0.x) SP 1 und höhere Versionen.
Gibt die Assembly und die Methode an, auf die der erstellte Funktionsname verweisen soll.
assembly_name muss einem Wert in der Spalte
name
vonSELECT * FROM sys.assemblies;
entsprechen.Der Name, der für die
CREATE ASSEMBLY
Anweisung verwendet wurde.class_name muss einem Wert in der Spalte
assembly_name
vonSELECT * FROM sys.assembly_modules;
entsprechen.Häufig enthält der Wert einen Punkt. In solchen Fällen erfordert die Transact-SQL-Syntax, dass der Wert mit einem Paar eckigen Klammern () oder mit einem Paar doppelter Anführungszeichen (
[]
""
) begrenzt ist.method_name muss einem Wert in der Spalte
method_name
vonSELECT * FROM sys.assembly_modules;
entsprechen.Die Methode muss statisch sein.
In einem typischen Beispiel für MyFood.dll
, in dem sich alle Typen im MyFood
Namespace befinden, könnte der EXTERNAL NAME
Wert sein MyFood.[MyFood.MyClass].MyStaticMethod
.
Standardmäßig kann SQL Server keinen CLR-Code ausführen. Sie können Datenbankobjekte erstellen, ändern und ablegen, die auf Common Language Runtime-Module verweisen. Sie können diese Verweise jedoch erst in SQL Server ausführen, wenn Sie die Option "clr enabled" aktivieren. Verwenden Sie dazu sp_configure. Diese Option ist in einer enthaltenen Datenbank nicht verfügbar.
<> table_type_definition ( { <column_definition><column_constraint | <> computed_column_definition } [ <table_constraint> ] [ , ...n ] )
Dieses Argument definiert den Tabellendatentyp für eine Transact-SQL-Funktion. Die Tabellendeklaration schließt Spaltendefinitionen und Spalten- oder Tabelleneinschränkungen ein. Die Tabelle wird immer in der primären Dateigruppe abgelegt.
<> clr_table_type_definition ( { column_namedata_type } [ , ...n ] )
Gilt für: SQL Server 2008 (10.0.x) SP 1 und höher sowie Azure SQL-Datenbank (Vorschau in einigen Regionen).
Definiert die Tabellendatentypen für eine CLR-Funktion. Die Tabellendeklaration schließt nur Spaltennamen und Datentypen ein. Die Tabelle wird immer in der primären Dateigruppe abgelegt.
NULL | NOT NULL
Nur für nativ kompilierte, benutzerdefinierte Skalarfunktionen unterstützt. Weitere Informationen dazu finden Sie unter Benutzerdefinierte Skalarfunktionen für In-Memory-OLTP.
NATIVE_COMPILATION
Gibt an, ob eine benutzerdefinierte Funktion nativ kompiliert wird. Dieses Argument ist für nativ kompilierte, benutzerdefinierte Skalarfunktionen erforderlich.
BEGIN ATOMIC WITH
Erforderlich und nur unterstützt für systemeigene kompilierte skalare benutzerdefinierte Funktionen. Weitere Informationen finden Sie unter Atomblöcke in nativen Prozeduren.
SCHEMABINDING
Das SCHEMABINDING
Argument ist für systemeigene kompilierte, skalare benutzerdefinierte Funktionen erforderlich.
EXECUTE AS
EXECUTE AS
ist für systemeigene kompilierte, skalare benutzerdefinierte Funktionen erforderlich.
<> function_option ::= und <clr_function_option> ::=
Gibt an, dass die Funktion über eine oder mehrere der folgenden Optionen verfügt.
ENCRYPTION
Gilt für: SQL Server 2008 (10.0.x) SP 1 und höhere Versionen.
Gibt an, dass der Datenbank-Engine den ursprünglichen Text der CREATE FUNCTION
Anweisung in ein verschleiertes Format konvertiert. Die Ausgabe der Verschleierung ist in katalogansichten nicht direkt sichtbar. Benutzer, die keinen Zugriff auf Systemtabellen oder Datenbankdateien haben, können den verschleierten Text nicht abrufen. Der Text ist jedoch für privilegierte Benutzer verfügbar, die entweder über die Diagnoseverbindung für Datenbankadministratoren auf Systemtabellen zugreifen oder direkt auf Datenbankdateien zugreifen können. Des Weiteren können Benutzer, die einen Debugger an den Serverprozess anfügen können, die Originalprozedur zur Laufzeit vom Arbeitsspeicher abrufen. Weitere Informationen zu Berechtigungen zum Zugreifen auf Systemmetadaten finden Sie unter Konfigurieren der Sichtbarkeit von Metadaten.
Mit dieser Option verhindern Sie, dass die Funktion als Teil der SQL Server-Replikation veröffentlicht wird. Diese Option kann für CLR-Funktionen nicht angegeben werden.
SCHEMABINDING
Gibt an, dass die Funktion an die Datenbankobjekte gebunden ist, auf die sie verweist. Wenn SCHEMABINDING
angegeben, können die Basisobjekte nicht auf eine Weise geändert werden, die sich auf die Funktionsdefinition auswirkt. Zunächst muss die Funktionsdefinition selbst geändert oder gelöscht werden, um Abhängigkeiten in dem zu ändernden Objekt zu entfernen.
Die Bindung der Funktion an die Objekte, auf die sie verweist, wird nur bei einer der folgenden Aktionen entfernt:
- Die Funktion wird gelöscht.
- Die Funktion wird mithilfe der
ALTER
-Anweisung geändert, wobei die OptionSCHEMABINDING
nicht angegeben ist.
Eine Funktion kann nur dann schemagebunden sein, wenn die folgenden Bedingungen erfüllt sind:
- Die Funktion ist eine Transact-SQL-Funktion.
- Die benutzerdefinierten Funktionen und Sichten, auf die die Funktion verweist, sind ebenfalls schemagebunden.
- In der Funktion sind die Verweise auf Objekte mithilfe von zweiteiligen Namen angegeben.
- Die Funktion und die Objekte, auf die sie verweist, gehören zu derselben Datenbank.
- Der Benutzer, der die
CREATE FUNCTION
-Anweisung ausgeführt hat, besitztREFERENCES
-Berechtigungen für die Datenbankobjekte, auf die die Funktion verweist.
RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
Gibt das OnNULLCall
Attribut einer skalaren Funktion an. Wenn nicht angegeben, CALLED ON NULL INPUT
wird standardmäßig impliziert. Mit anderen Worten, der Funktionstext wird auch dann ausgeführt, wenn NULL
er als Argument übergeben wird.
Wenn RETURNS NULL ON NULL INPUT
in einer CLR-Funktion angegeben wird, gibt sie an, dass SQL Server zurückgeben NULL
kann, wenn eines der von ihr empfangenen Argumente zurückgegeben wird NULL
, ohne tatsächlich den Textkörper der Funktion aufzugeben. Wenn die Methode einer in <method_specifier>
dieser Eigenschaft angegebenen CLR-Funktion bereits ein benutzerdefiniertes Attribut aufweist, das angibt, die Anweisung jedoch CREATE FUNCTION
angibtRETURNS NULL ON NULL INPUT
CALLED ON NULL INPUT
, hat die CREATE FUNCTION
Anweisung Vorrang. Das OnNULLCall
Attribut kann für CLR-Tabellenwertfunktionen nicht angegeben werden.
EXECUTE AS
Gibt den Sicherheitskontext an, in dem die benutzerdefinierte Funktion ausgeführt wird. Deshalb können Sie steuern, welches Benutzerkonto SQL Server verwendet, um Berechtigungen für eines der Datenbankobjekte zu überprüfen, auf die in der Funktion verwiesen wird.
EXECUTE AS
kann für Inlinetabellenwertfunktionen nicht angegeben werden.
Weitere Informationen finden Sie unter EXECUTE AS-Klausel (Transact-SQL).
INLINE = { ON | OFF }
Gilt für: SQL Server 2019 (15.x) und höhere Versionen und Azure SQL-Datenbank.
Gibt an, ob für diese benutzerdefinierte Skalarfunktion ein Inlining durchgeführt werden sollte. Diese Klausel gilt nur für benutzerdefinierte Skalarfunktionen. Die INLINE
Klausel ist nicht obligatorisch. Wenn die INLINE
Klausel nicht angegeben ist, wird sie automatisch auf ON
oder OFF
basierend darauf festgelegt, ob die UDF inlineierbar ist. Wenn INLINE = ON
angegeben wird, die UDF jedoch nicht inlineierbar ist, wird ein Fehler ausgelöst. Weitere Informationen finden Sie unter Scalar UDF Inlining (Inlining benutzerdefinierter Skalarfunktionen).
<> column_definition ::=
Definiert den Tabellendatentyp. Die Tabellendeklaration schließt Spaltendefinitionen und Einschränkungen ein. Für CLR-Funktionen können nur column_name und data_type angegeben werden.
column_name
Der Name einer Spalte in der Tabelle. Spaltennamen müssen den Regeln für Bezeichner entsprechen und in der Tabelle eindeutig sein. column_name kann zwischen 1 und 128 Zeichen aufweisen.
data_type
Gibt den Datentyp der Spalte an. Für Transact-SQL-Funktionen sind abgesehen von timestamp alle Datentypen zulässig, einschließlich benutzerdefinierter CLR-Typen. Für CLR-Funktionen sind alle Datentypen, einschließlich benutzerdefinierter CLR-Typen, mit Ausnahme von Text, ntext, Bild, Zeichen, Varchar, varchar(max)und Zeitstempel zulässig. Der Nichtkalartypcursor kann nicht als Spaltendatentyp in Transact-SQL- oder CLR-Funktionen angegeben werden.
DEFAULT constant_expression
Gibt den Wert an, der für die Spalte bereitgestellt wird, wenn kein Wert explizit angegeben wurde. constant_expression ist eine Konstante oder NULL
ein Systemfunktionswert. DEFAULT
Definitionen können auf jede Spalte angewendet werden, mit Ausnahme von Definitionen, die über die IDENTITY
Eigenschaft verfügen. DEFAULT
kann für CLR-Tabellenwertfunktionen nicht angegeben werden.
COLLATE collation_name
Gibt die Sortierung für die Spalte an. Wenn keine Sortierung angegeben ist, wird der Spalte die Standardsortierung der Datenbank zugewiesen. Als Sortierungsname kann entweder der Name einer Windows-Sortierreihenfolge oder ein SQL-Sortierungsname verwendet werden. Eine Liste und weitere Informationen zu Sortierungen finden Sie unter Name der Windows-Sortierung (Transact-SQL) und SQL Server-Sortierungsname (Transact-SQL).
Die COLLATE
Klausel kann verwendet werden, um nur die Sortierungen der Datentypen Char, varchar, nchar und nvarchar zu ändern. COLLATE
kann für CLR-Tabellenwertfunktionen nicht angegeben werden.
ROWGUIDCOL
Gibt an, dass die neue Spalte eine Spalte mit für alle Zeilen global eindeutigen Bezeichnern ist. Pro Tabelle kann nur eine Eindeutigidentifiziererspalte als ROWGUIDCOL
Spalte festgelegt werden. Die ROWGUIDCOL
Eigenschaft kann nur einer eindeutigen Identifikationsspalte zugewiesen werden.
Die ROWGUIDCOL
Eigenschaft erzwingt keine Eindeutigkeit der in der Spalte gespeicherten Werte. Außerdem werden nicht automatisch Werte für neue Zeilen generiert, die in die Tabelle eingefügt werden. Verwenden Sie die NEWID
Funktion für INSERT
Anweisungen, um eindeutige Werte für jede Spalte zu generieren. Ein Standardwert kann angegeben werden; NEWID
Kann jedoch nicht als Standard angegeben werden.
IDENTITY
Gibt an, dass es sich bei der neuen Spalte um eine Identitätsspalte handelt. Wenn der Tabelle eine neue Zeile hinzugefügt wird, stellt SQL Server einen eindeutigen, inkrementellen Wert für die Spalte bereit. Identitätsspalten werden in der Regel zusammen mit PRIMARY KEY
Einschränkungen verwendet, die als eindeutiger Zeilenbezeichner für die Tabelle dienen. Die Eigenschaft IDENTITY
kann folgenden Spalten zugewiesen werden: tinyint, smallint, int, bigint, decimal(p,0) oder numeric(p,0). Es kann nur eine Identitätsspalte pro Tabelle erstellt werden. Gebundene Standardwerte und DEFAULT
Einschränkungen können nicht mit einer Identitätsspalte verwendet werden. Sie müssen entweder seed und den increment oder keinen von beiden angeben. Wurden Ausgangswert und inkrementeller Wert nicht angegeben, ist der Standardwert (1,1).
IDENTITY
kann für CLR-Tabellenwertfunktionen nicht angegeben werden.
seed
Der ganzzahlige Wert, der der ersten Zeile in der Tabelle zugewiesen werden soll.
increment
Der ganzzahlige Wert, der dem Ausgangswert für aufeinander folgende Zeilen in der Tabelle hinzugefügt werden soll.
<> column_constraint ::= und <table_constraint> ::=
Definiert die Einschränkung für eine bestimmte Spalte oder Tabelle. Für CLR-Funktionen ist NULL
der einzige zulässige Einschränkungstyp zulässig. Benannte Einschränkungen sind nicht zulässig.
NULL | NOT NULL
Bestimmt, ob Nullwerte in der Spalte zulässig sind. NULL
ist nicht unbedingt eine Einschränkung, kann aber genau wie NOT NULL
angegeben werden. NOT NULL
kann für CLR-Tabellenwertfunktionen nicht angegeben werden.
PRIMARY KEY
Eine Einschränkung, die die Entitätsintegrität für eine angegebene Spalte über einen eindeutigen Index erzwingt. In benutzerdefinierten Funktionen mit Tabellenwert kann die PRIMARY KEY
Einschränkung nur für eine Spalte pro Tabelle erstellt werden. PRIMARY KEY
kann für CLR-Tabellenwertfunktionen nicht angegeben werden.
UNIQUE
Eine Einschränkung, die Entitätsintegrität für eine angegebene Spalte (oder Spalten) durch einen eindeutigen Index bereitstellt. Eine Tabelle kann mehrere UNIQUE
Einschränkungen aufweisen. UNIQUE
kann für CLR-Tabellenwertfunktionen nicht angegeben werden.
CLUSTERED | NONCLUSTERED
Gibt an, dass für die PRIMARY KEY
oder UNIQUE
die Einschränkung ein gruppierter oder nicht gruppierter Index erstellt wird. PRIMARY KEY
Constraints use CLUSTERED
, and UNIQUE
constraints use NONCLUSTERED
.
CLUSTERED
kann nur für eine Einschränkung angegeben werden. Wenn CLUSTERED
für eine UNIQUE
Einschränkung angegeben und eine PRIMARY KEY
Einschränkung ebenfalls angegeben wird, wird die PRIMARY KEY
Verwendung verwendet NONCLUSTERED
.
CLUSTERED
und NONCLUSTERED
kann für CLR-Tabellenwertfunktionen nicht angegeben werden.
CHECK
Eine Einschränkung, die Domänenintegrität erzwingt, indem die möglichen Eingabewerte für eine oder mehrere Spalten beschränkt wird. CHECK
Einschränkungen können für CLR-Tabellenwertfunktionen nicht angegeben werden.
logical_expression
Ein logischer Ausdruck, der zurückgibt TRUE
oder FALSE
.
<> computed_column_definition ::=
Gibt eine berechnete Spalte an. Weitere Informationen zu berechneten Spalten finden Sie unter CREATE TABLE (Transact-SQL).
column_name
Der Name der berechneten Spalte.
computed_column_expression
Ein Ausdruck, der den Wert einer berechneten Spalte definiert.
<index_option> ::=
Gibt die Indexoptionen für den Index oder UNIQUE
den PRIMARY KEY
Index an. Weitere Informationen zu Indexoptionen finden Sie unter CREATE INDEX (Transact-SQL).
PAD_INDEX = { ON | OFF }
Gibt die Auffüllung von Indizes an. Der Standardwert ist OFF
.
FILLFACTOR = fillfactor
Gibt einen Prozentsatz an, der anzeigt, wie weit Datenbank-Engine die Blattebene jeder Indexseite während der Indexerstellung oder -änderung füllen soll. fillfactor muss ein ganzzahliger Wert zwischen 1 und 100 sein. Die Standardeinstellung ist 0.
IGNORE_DUP_KEY = { ON | OFF }
Gibt die Fehlermeldung an, wenn ein Einfügevorgang versucht, doppelte Schlüsselwerte in einen eindeutigen Index einzufügen. Die IGNORE_DUP_KEY
-Option gilt nur für Einfügevorgänge nach dem Erstellen oder Neuerstellen des Index. Der Standardwert ist OFF
.
STATISTICS_NORECOMPUTE = { ON | OFF}
Gibt an, ob Verteilungsstatistiken neu berechnet werden. Der Standardwert ist OFF
.
ALLOW_ROW_LOCKS = { ON | OFF }
Gibt an, ob Zeilensperren zulässig sind. Der Standardwert ist ON
.
ALLOW_PAGE_LOCKS = { ON | OFF }
Gibt an, ob Seitensperren zulässig sind. Der Standardwert ist ON
.
Bewährte Methoden
Wenn eine benutzerdefinierte Funktion nicht mit der SCHEMABINDING
Klausel erstellt wird, können sich Änderungen, die an zugrunde liegenden Objekten vorgenommen werden, auf die Definition der Funktion auswirken und unerwartete Ergebnisse erzeugen, wenn sie aufgerufen wird. Es wird empfohlen, eine der folgenden Methoden zu implementieren, um sicherzustellen, dass die Funktion nicht aufgrund von Änderungen an den zugrunde liegenden Objekten veraltet:
Geben Sie die
WITH SCHEMABINDING
Klausel an, wenn Sie die Funktion erstellen. Diese Option stellt sicher, dass die objekte, auf die in der Funktionsdefinition verwiesen wird, nicht geändert werden können, es sei denn, die Funktion wird ebenfalls geändert.Führen Sie die gespeicherte Prozedur sp_refreshsqlmodule aus, nachdem Sie ein Objekt geändert haben, das in der Definition der Funktion angegeben ist.
Weitere Informationen und Leistungsüberlegungen zu inlinetabellenwertigen Funktionen (Inline-TVFs) und mehrwertigen Funktionen (MSTVFs) finden Sie unter Erstellen von benutzerdefinierten Funktionen (Datenbank-Engine).
Datentypen
Wenn Parameter in einer CLR-Funktion angegeben werden, sollte es sich dabei um SQL Server-Typen handeln, wie weiter oben für scalar_parameter_data_type definiert. Weitere Informationen zum Vergleichen von SQL Server-Systemdatentypen mit CLR-Integrationsdatentypen oder .NET Framework-Datentypen für common language runtime finden Sie unter Zuordnung von CLR-Parameterdaten.
Damit SQL Server auf die richtige Methode verweist, wenn sie in einer Klasse überladen ist, muss die angegebene <method_specifier>
Methode die folgenden Merkmale aufweisen:
- Empfangen Sie die gleiche Anzahl von Parametern wie in
[ , ...n ]
. - Alle Parameter nach Wert erhalten, nicht nach Verweis.
- Verwenden Sie Parametertypen, die mit typen kompatibel sind, die in der SQL Server-Funktion angegeben sind.
Wenn der Rückgabedatentyp der CLR-Funktion einen Tabellentyp (RETURNS TABLE
) angibt, sollte der Rückgabedatentyp der Methode <method_specifier>
vom Typ IEnumerator
oder IEnumerable
, und es wird davon ausgegangen, dass die Schnittstelle vom Ersteller der Funktion implementiert wird. Im Gegensatz zu Transact-SQL-Funktionen können CLR-Funktionen keine Einschränkungen oder CHECK
Einschränkungen enthaltenPRIMARY KEY
UNIQUE
<table_type_definition>
. Die Datentypen der Spalten, die in <table_type_definition>
angegeben werden, müssen mit den Typen der entsprechenden Spalten des Resultsets übereinstimmen, das von der in <method_specifier>
angegebenen Methode zur Ausführungszeit zurückgegeben wird. Diese Typüberprüfung wird zum Zeitpunkt der Erstellung der Funktion nicht ausgeführt.
Weitere Informationen zum Programmieren von CLR-Funktionen finden Sie unter CLR-benutzerdefinierte Funktionen.
Hinweise
Skalare Funktionen können aufgerufen werden, wo skalare Ausdrücke verwendet werden, einschließlich berechneter Spalten und CHECK
Einschränkungsdefinitionen. Skalare Funktionen können auch mithilfe der EXECUTE -Anweisung (Transact-SQL) ausgeführt werden. Skalarfunktionen müssen mindestens mit dem zweiteiligen Namen der Funktion (<schema>.<function>
) aufgerufen werden. Weitere Informationen zu mehrteiligen Namen finden Sie unter Transact-SQL-Syntaxkonventionen (Transact-SQL). Tabellenwertfunktionen können dort aufgerufen werden, wo Tabellenausdrücke in der FROM
-Klausel der Anweisungen SELECT
, INSERT
, UPDATE
oder DELETE
zulässig sind. Weitere Informationen finden Sie unter Ausführen von benutzerdefinierten Funktionen.
Interoperabilität
Die folgenden Anweisungen sind in einer Funktion zulässig:
- Zuweisungsanweisungen
- Anweisungen zur Ablaufsteuerung mit Ausnahme von
TRY...CATCH
-Anweisungen. DECLARE
-Anweisungen zum Definieren lokaler Datenbankvariablen und lokaler Cursor.SELECT
-Anweisungen, die Auswahllisten mit Ausdrücken enthalten, die lokalen Variablen Werte zuweisen.- Cursorvorgänge, die auf lokale Cursor verweisen, die in der Funktion deklariert, geöffnet und geschlossen werden und deren Zuordnungen in der Funktion aufgehoben werden. Nur
FETCH
Anweisungen, die lokalen Variablen mithilfe derINTO
Klausel Werte zuweisen, sind zulässig.FETCH
Anweisungen, die Daten an den Client zurückgeben, sind nicht zulässig. INSERT
-,UPDATE
- undDELETE
-Anweisungen, die lokale Tabellenvariablen ändern.EXECUTE
-Anweisungen, die erweiterte gespeicherte Prozeduren aufrufen.
Weitere Informationen finden Sie unter Erstellen von benutzerdefinierten Funktionen (Datenbank-Engine).For more information, see Create user-defined functions (Datenbank-Engine).
Berechnete Spalteninteroperabilität
Funktionen verfügen über die folgenden Eigenschaften. Die Werte dieser Eigenschaften bestimmen, ob Funktionen in permanent berechneten oder indizierten berechneten Spalten verwendet werden können.
Eigenschaft | BESCHREIBUNG | Hinweise |
---|---|---|
IsDeterministic |
Die Funktion ist deterministisch oder nicht deterministisch. | Lokaler Datenzugriff ist in deterministischen Funktionen zulässig. Funktionen, die immer dasselbe Ergebnis zurückgeben, wenn sie mit einem bestimmten Satz von Eingabewerten aufgerufen werden, und mit demselben Status der Datenbank würden deterministisch bezeichnet. |
IsPrecise |
Die Funktion ist präzise oder unpräzise. | Unpräzise Funktionen enthalten Vorgänge wie Gleitkommatransaktionen. |
IsSystemVerified |
Die Präzisions- und Determinismuseigenschaften der Funktion können von SQL Server geprüft werden. | |
SystemDataAccess |
Die Funktion greift auf Systemdaten (Systemkataloge oder virtuelle Systemtabellen) in der lokalen Instanz von SQL Server zu. | |
UserDataAccess |
Die Funktion greift auf Benutzerdaten in der lokalen Instanz von SQL Server zu. | Schließt benutzerdefinierte Tabellen und temporäre Tabellen ein, jedoch keine Tabellenvariablen. |
Die Präzisions- und Determinismuseigenschaften von Transact-SQL-Funktionen werden automatisch von SQL Server bestimmt. Die Datenzugriffs- und Determinismuseigenschaften von CLR-Funktionen können vom Benutzer angegeben werden. Weitere Informationen finden Sie unter CLR-Integration: benutzerdefinierte Attribute für CLR-Routinen.
Verwenden Sie OBJECTPROPERTYEX (Transact-SQL), um die aktuellen Werte für diese Eigenschaften anzuzeigen.
Wichtig
Damit Funktionen deterministisch sind, müssen sie mit SCHEMABINDING
erstellt werden.
Eine berechnete Spalte, die eine benutzerdefinierte Funktion aufruft, kann in einem Index verwendet werden, sofern die benutzerdefinierte Funktion über folgende Eigenschaftswerte verfügt:
IsDeterministic
isttrue
.IsSystemVerified
isttrue
(es sei denn, die berechnete Spalte wird beibehalten)UserDataAccess
ist gleichfalse
.SystemDataAccess
ist gleichfalse
.
Weitere Informationen finden Sie unter "Indizes für berechnete Spalten".
Aufrufen erweiterter gespeicherter Prozeduren aus Funktionen
Die erweiterte gespeicherte Prozedur kann beim Aufrufen innerhalb einer Funktion keine Resultsets an den Client zurückgeben. Alle ODS-APIs, die Resultsets an den Client zurückgeben, geben zurück FAIL
. Die erweiterte gespeicherte Prozedur könnte eine Verbindung mit einer Instanz von SQL Server herstellen; Es sollte jedoch nicht versuchen, dieselbe Transaktion wie die Funktion zu verbinden, die die erweiterte gespeicherte Prozedur aufgerufen hat.
Ähnlich wie aufrufe aus einem Batch oder einer gespeicherten Prozedur wird die erweiterte gespeicherte Prozedur im Kontext des Windows-Sicherheitskontos ausgeführt, unter dem SQL Server ausgeführt wird. Der Besitzer der gespeicherten Prozedur sollte dieses Szenario berücksichtigen, wenn EXECUTE
er benutzern die Berechtigung erteilt.
Begrenzungen
Mit benutzerdefinierten Funktionen können keine Aktionen ausgeführt werden, die den Status einer Datenbank ändern.
Benutzerdefinierte Funktionen dürfen keine OUTPUT INTO
-Klausel enthalten, deren Ziel eine Tabelle ist.
Die folgenden Service Broker-Anweisungen können nicht in die Definition einer benutzerdefinierten Transact-SQL-Funktion eingeschlossen werden:
BEGIN DIALOG CONVERSATION
END CONVERSATION
GET CONVERSATION GROUP
MOVE CONVERSATION
RECEIVE
SEND
Benutzerdefinierte Funktionen können geschachtelt werden. Dies bedeutet, dass eine benutzerdefinierte Funktion eine andere aufrufen kann. Die Schachtelungsebene wird um eins erhöht, wenn die aufgerufene Funktion mit der Ausführung beginnt, und wird wieder um eins erniedrigt, wenn die aufgerufene Funktion die Ausführung beendet. Benutzerdefinierte Funktionen unterstützen bis zu 32 geschachtelte Ebenen. Ein Überschreiten der maximalen Schachtelungsebenen verursacht das Fehlschlagen der gesamten Funktionsaufrufskette. Alle Verweise auf verwalteten Code von einer benutzerdefinierten Transact-SQL-Funktion aus gelten hinsichtlich des Maximums von 32 Schachtelungsebenen als eine Ebene. Methoden, die aus verwaltetem Code aufgerufen werden, werden nicht mitgezählt.
Verwenden der Sortierreihenfolge in CLR-Tabellenwertfunktionen
Beachten Sie die folgenden Richtlinien, wenn Sie die ORDER
-Klausel in CLR-Tabellenwertfunktionen verwenden:
Sie müssen sicherstellen, dass die Ergebnisse immer in der angegebenen Reihenfolge sortiert werden. Wenn sich die Ergebnisse nicht in der angegebenen Reihenfolge befinden, generiert SQL Server beim Ausführen der Abfrage eine Fehlermeldung.
Wenn eine
ORDER
-Klausel angegeben wird, muss die Ausgabe der Tabellenwertfunktion entsprechend der Spaltensortierung (explizit oder implizit) sortiert sein. Wenn die Spaltensortierung beispielsweise chinesisch ist, müssen die zurückgegebenen Ergebnisse nach chinesischen Sortierregeln sortiert werden. (Die Sortierung wird entweder in der DDL für die Tabellenwertfunktion angegeben oder aus der Datenbanksortierung abgerufen.)SQL Server überprüft die
ORDER
Klausel immer, wenn angegeben, während Ergebnisse zurückgegeben werden, unabhängig davon, ob der Abfrageprozessor sie verwendet, um weitere Optimierungen durchzuführen. Verwenden Sie dieORDER
Klausel nur, wenn Sie wissen, dass es für den Abfrageprozessor nützlich ist.Der SQL Server-Abfrageprozessor verwendet die
ORDER
-Klausel automatisch in den folgenden Fällen:- Bei Einfügeabfragen, bei denen die
ORDER
-Klausel mit einem Index kompatibel ist. - Bei
ORDER BY
-Klauseln, die mit derORDER
-Klausel kompatibel sind. - Bei Aggregaten, bei denen
GROUP BY
mit derORDER
-Klausel kompatibel ist. - Bei
DISTINCT
-Aggregaten, bei denen verschiedene Spalten mit derORDER
-Klausel kompatibel sind.
- Bei Einfügeabfragen, bei denen die
Die ORDER
Klausel garantiert keine sortierten Ergebnisse, wenn eine SELECT
Abfrage ausgeführt wird, es sei denn ORDER BY
, sie wird auch in der Abfrage angegeben. Informationen zu Abfragen nach Spalten, die in der Sortierreihenfolge für Tabellenwertfunktionen enthalten sind, finden Sie unter sys.function_order_columns (Transact-SQL).
Metadaten
In der folgenden Tabelle werden die Systemkatalogsichten aufgelistet, die Sie verwenden können, um Metadaten zu benutzerdefinierten Funktionen zurückzugeben.
Systemsicht | BESCHREIBUNG |
---|---|
sys.sql_modules | Siehe Beispiel E im Abschnitt "Beispiele". |
sys.assembly_modules | Zeigt Informationen zu benutzerdefinierten CLR-Funktionen an. |
sys.parameters | Zeigt Informationen zu den Parametern an, die in benutzerdefinierten Funktionen definiert sind. |
sys.sql_expression_dependencies | Zeigt die zugrunde liegenden Objekte an, auf die von einer Funktion verwiesen wird. |
Berechtigungen
Erfordert die CREATE FUNCTION
-Berechtigung in der Datenbank und die ALTER
-Berechtigung für das Schema, in dem die Funktion erstellt wird. Wenn die Funktion einen benutzerdefinierten Typ angibt, wird die EXECUTE
-Berechtigung für den Typ benötigt.
Beispiele
Weitere Beispiele und Leistungsüberlegungen zu UDFs finden Sie unter Erstellen von benutzerdefinierten Funktionen (Datenbank-Engine).
A. Verwenden einer skalarwertigen benutzerdefinierten Funktion, die die ISO-Woche berechnet
Im folgenden Beispiel wird die benutzerdefinierte Funktion ISOweek
erstellt. Diese Funktion nimmt ein Datumsargument an und berechnet die Nummer der ISO-Woche. Damit diese Funktion richtig rechnet, muss SET DATEFIRST 1
aufgerufen werden, bevor die Funktion aufgerufen wird.
Das Beispiel zeigt auch die Verwendung der EXECUTE AS-Klausel (Transact-SQL) zum Angeben des Sicherheitskontexts, in dem eine gespeicherte Prozedur ausgeführt werden kann. Im Beispiel gibt die Option CALLER
an, dass die Prozedur im Kontext des Benutzers ausgeführt wird, der sie aufruft. Zusätzlich können Sie die Optionen SELF
, OWNER
und user_name angeben.
Hier sehen Sie den Funktionsaufruf. Für DATEFIRST
ist 1
festgelegt.
CREATE FUNCTION dbo.ISOweek (@DATE DATETIME)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ISOweek INT;
SET @ISOweek = DATEPART(wk, @DATE) + 1 -
DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104');
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek = 0)
SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4))
+ '12' + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1;
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm, @DATE) = 12)
AND ((DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28))
SET @ISOweek = 1;
RETURN (@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME, '12/26/2004', 101)) AS 'ISO Week';
Hier ist das Resultset.
ISO Week
----------------
52
B. Erstellen einer Inline-Tabellenwertfunktion
Im folgenden Beispiel wird eine Inline-Tabellenwertfunktion in der AdventureWorks2022-Datenbank zurückgegeben. Die Funktion gibt drei Spalten ProductID
, Name
und das Aggregat der gesamten Verkäufe des Jahres (nach Filiale sortiert) als YTD Total
für jedes Produkt zurück, das an die Filiale verkauft wurde.
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid INT)
RETURNS TABLE
AS
RETURN (
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
FROM Production.Product AS P
INNER JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
INNER JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
INNER JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);
GO
Rufen Sie die Funktion mit dieser Abfrage auf.
SELECT * FROM Sales.ufn_SalesByStore (602);
C. Erstellen einer Mehrwertfunktion mit mehreren Anweisungen
Im folgenden Beispiel wird eine Inline-Tabellenwertfunktion fn_FindReports(InEmpID)
in der AdventureWorks2022
-Datenbank erstellt. Wenn der Funktion eine gültige Mitarbeiter-ID bereitgestellt wird, gibt sie eine Tabelle zurück, die allen Mitarbeitern entspricht, die dem Mitarbeiter entweder direkt oder indirekt unterstellt sind. Die Funktion verwendet eine rekursive Abfrage (Common Table Expression, CTE), um eine hierarchische Mitarbeiterliste zu erstellen. Weitere Informationen zu rekursiven CTEs finden Sie unter WITH common_table_expression (Transact-SQL).
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
RETURNS @retFindReports TABLE (
EmployeeID INT PRIMARY KEY NOT NULL,
FirstName NVARCHAR(255) NOT NULL,
LastName NVARCHAR(255) NOT NULL,
JobTitle NVARCHAR(50) NOT NULL,
RecursionLevel INT NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.
AS
BEGIN
WITH EMP_cte (
EmployeeID,
OrganizationNode,
FirstName,
LastName,
JobTitle,
RecursionLevel
) -- CTE name and columns
AS (
-- Get the initial list of Employees for Manager n
SELECT e.BusinessEntityID,
OrganizationNode = ISNULL(e.OrganizationNode, CAST('/' AS HIERARCHYID)),
p.FirstName,
p.LastName,
e.JobTitle,
0
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = @InEmpID
UNION ALL
-- Join recursive member to anchor
SELECT e.BusinessEntityID,
e.OrganizationNode,
p.FirstName,
p.LastName,
e.JobTitle,
RecursionLevel + 1
FROM HumanResources.Employee e
INNER JOIN EMP_cte
ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
)
-- Copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID,
FirstName,
LastName,
JobTitle,
RecursionLevel
FROM EMP_cte
RETURN
END;
GO
-- Example invocation
SELECT EmployeeID,
FirstName,
LastName,
JobTitle,
RecursionLevel
FROM dbo.ufn_FindReports(1);
GO
D: Erstellen einer CLR-Funktion
In diesem Beispiel wird die CLR-Funktion len_s
erstellt. Bevor die Funktion erstellt wird, wird die Assembly SurrogateStringFunction.dll
in der lokalen Datenbank registriert.
Gilt für: SQL Server 2008 (10.0.x) SP 1 und höhere Versionen.
DECLARE @SamplesPath NVARCHAR(1024);
-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name,
'Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf',
'Microsoft SQL Server\130\Samples\Engine\Programmability\CLR\'
)
FROM master.sys.database_files
WHERE name = 'master';
CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE FUNCTION [dbo].[len_s] (@str NVARCHAR(4000))
RETURNS BIGINT
AS
EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO
Ein Beispiel zum Erstellen einer CLR-Tabellenwertfunktionen finden Sie unter CLR-Tabellenwertfunktionen.
E. Anzeigen der Definition benutzerdefinierter Funktionen
SELECT DEFINITION,
type
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.object_id = o.object_id
AND type IN ('FN', 'IF', 'TF');
GO
Die Definition von Funktionen, die mithilfe der ENCRYPTION
Option erstellt wurden, kann nicht mithilfe sys.sql_modules
der Option angezeigt werden. Andere Informationen zu den verschlüsselten Funktionen werden jedoch angezeigt.
Zugehöriger Inhalt
- Erstellen benutzerdefinierter Funktionen (Datenbank-Engine)
- ALTER FUNCTION (Transact-SQL)
- DROP FUNCTION (Transact-SQL)
- OBJECTPROPERTYEX (Transact-SQL)
- sys.sql_modules (Transact-SQL)
- sys.assembly_modules (Transact-SQL)
- EXECUTE (Transact-SQL)
- CLR-benutzerdefinierte Funktionen
- EVENTDATA (Transact-SQL)
- CREATE SECURITY POLICY (Transact-SQL)