Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
Gilt für:
SQL ServerAzure SQL-DatenbankVerwaltete Azure SQL-InstanzAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-Analyseendpunkt in Microsoft FabricLagerhaus in Microsoft FabricSQL-Datenbank in Microsoft Fabric Preview
Variablen werden im Textkörper eines Batches oder einer Prozedur mit der DECLARE Anweisung deklariert und werden Mithilfe einer oder SELECT einer SET Anweisung Werte zugewiesen. Cursorvariablen können mit dieser Anweisung deklariert und mit anderen cursorspezifischen Anweisungen verwendet werden. Nach der Deklaration werden alle Variablen als NULLinitialisiert, es sei denn, ein Wert wird als Teil der Deklaration bereitgestellt.
Transact-SQL-Syntaxkonventionen
Syntax
Die folgende Syntax gilt für SQL Server und Azure SQL-Datenbank:
DECLARE
{
{ @local_variable [AS] data_type [ = value ] }
| { @cursor_variable_name CURSOR }
| { @table_variable_name [AS] <table_type_definition> }
} [ , ...n ]
<table_type_definition> ::=
TABLE ( { <column_definition> | <table_constraint> | <table_index> } } [ , ...n ] )
<column_definition> ::=
column_name { scalar_data_type | AS computed_column_expression }
[ COLLATE collation_name ]
[ [ DEFAULT constant_expression ] | IDENTITY [ (seed, increment ) ] ]
[ ROWGUIDCOL ]
[ <column_constraint> ]
[ <column_index> ]
<column_constraint> ::=
{
[ NULL | NOT NULL ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] )
[ ON { filegroup | "default" } ]
| [ CHECK ( logical_expression ) ] [ , ...n ]
}
<column_index> ::=
INDEX index_name [ CLUSTERED | NONCLUSTERED ]
[ WITH ( <index_option> [ , ... n ] ) ]
[ ON { partition_scheme_name (column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
<table_constraint> ::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column_name [ ASC | DESC ] [ , ...n ]
[ WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , ...n ] )
| [ CHECK ( logical_expression ) ] [ , ...n ]
}
<table_index> ::=
{
{
INDEX index_name [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
(column_name [ ASC | DESC ] [ , ... n ] )
| INDEX index_name CLUSTERED COLUMNSTORE
| INDEX index_name [ NONCLUSTERED ] COLUMNSTORE ( column_name [ , ... n ] )
}
[ WITH ( <index_option> [ , ... n ] ) ]
[ ON { partition_scheme_name ( column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
}
<index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| STATISTICS_INCREMENTAL = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
| COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
| DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
[ ON PARTITIONS ( { partition_number_expression | <range> }
[ , ...n ] ) ]
| XML_COMPRESSION = { ON | OFF }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ] ]
}
Die folgende Syntax gilt für Azure Synapse Analytics, Parallel Data Warehouse und Microsoft Fabric:
DECLARE
{ { @local_variable [AS] data_type } [ = value [ COLLATE <collation_name> ] ] } [ , ...n ]
Arguments
@ local_variable
Der Name einer Variablen. Variablennamen müssen mit einem at-Zeichen (@) beginnen. Die Namen lokaler Variablen müssen den Regeln für Bezeichner entsprechen.
data_type
Ein beliebiger vom System bereitgestellter, benutzerdefinierter CRL-Tabellentyp (Common Language Runtime) oder ein Aliasdatentyp. Eine Variable kann nicht den Datentyp text, ntext oder image haben.
Weitere Informationen zu Systemdatentypen finden Sie unter Datentypen. Weitere Informationen zu benutzerdefinierten CLR-Typen oder Aliasdatentypen finden Sie unter CREATE TYPE.
= Wert
Weist der Variablen inline einen Wert zu. Der Wert kann eine Konstante oder ein Ausdruck sein; auf jeden Fall muss er mit dem Typ der Variablendeklaration übereinstimmen oder implizit in diesen Typ konvertiert werden können. Weitere Informationen finden Sie unter "Ausdrücke".
@ cursor_variable_name
Der Name einer Cursorvariablen. Cursorvariablennamen müssen mit einem at-Zeichen (@) beginnen und den Regeln für Bezeichner entsprechen.
CURSOR
Gibt an, dass die Variable eine lokale Cursorvariable ist.
@ table_variable_name
Der Name einer Variablen des Typs table. Variablennamen müssen mit einem at-Zeichen (@) beginnen und den Regeln für Bezeichner entsprechen.
<table_type_definition>
Definiert den Datentyp table. Die Tabellendeklaration schließt Spaltendefinitionen, Namen, Datentypen und Einschränkungen ein. Die einzigen zulässigen Einschränkungstypen sind
PRIMARY KEY, ,UNIQUE,NULLundCHECK. Ein Aliasdatentyp kann nicht als Skalardatentyp für Spalten verwendet werden, wenn eine Regel oder Standarddefinition an den Typ gebunden ist.
<table_type_definition>
Eine Teilmenge von Informationen, die zum Definieren einer Tabelle in CREATE TABLEverwendet werden. Darin sind Elemente und wichtige Definitionen eingeschlossen. Weitere Informationen finden Sie unter CREATE TABLE.
n
Ein Platzhalter, der angibt, dass mehrere Variablen angegeben und ihnen Werte zugewiesen werden können. Beim Deklarieren von Tabellenvariablen muss die Tabellenvariable die einzige Variable sein, die in der
DECLAREAnweisung deklariert wird.
column_name
Der Name der Spalte in der Tabelle.
scalar_data_type
Gibt an, dass die Spalte ein skalarer Datentyp ist.
computed_column_expression
Ein Ausdruck, der den Wert einer berechneten Spalte definiert. Sie wird aus einem Ausdruck berechnet, der andere Spalten in derselben Tabelle verwendet. Beispielsweise kann eine berechnete Spalte die Definition
cost AS price * qtyaufweisen. Der Ausdruck kann ein nicht komputiertes Spaltenname, eine Konstante, eine integrierte Funktion, eine Variable oder eine beliebige Kombination dieser Optionen sein, die von einem oder mehreren Operatoren verbunden sind. Der Ausdruck kann keine Unterabfrage oder benutzerdefinierte Funktion sein. Der Ausdruck kann nicht auf einen benutzerdefinierten CLR-Typ verweisen.
[ COLLATE collation_name ]
Gibt die Sortierung für die Spalte an. collation_name kann entweder ein Windows-Sortierungsname oder ein SQL-Sortierungsname sein und gilt nur für Spalten des Zeichens, varchar, text, nchar, nvarchar und ntext-Datentypen. Wenn collation_name nicht angegeben ist, wird der Spalte die Sortierung des benutzerdefinierten Datentyps zugewiesen, wenn es sich um eine Spalte von einem benutzerdefinierten Datentyp handelt, oder es wird die Sortierung der aktuellen Datenbank zugewiesen.
Weitere Informationen zu den Namen der Windows- und SQL-Sortierung finden Sie unter COLLATE.
DEFAULT
Gibt den Wert an, der für die Spalte bereitgestellt wird, wenn kein Wert explizit angegeben wurde.
DEFAULT Definitionen können auf alle Spalten angewendet werden, mit Ausnahme von Spalten, die als Zeitstempel oder mit der IDENTITY Eigenschaft definiert sind.
DEFAULT Definitionen werden entfernt, wenn die Tabelle gelöscht wird. Nur ein konstanter Wert, z. B. eine Zeichenfolge; eine Systemfunktion, z. B. ein SYSTEM_USER(); oder NULL kann als Standard verwendet werden. Um die Kompatibilität mit früheren Versionen von SQL Server aufrechtzuerhalten, kann ein Einschränkungsname einem DEFAULT.
constant_expression
Eine Konstante oder eine Systemfunktion,
NULLdie als Standardwert für die Spalte verwendet wird.
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 häufig mit PRIMARY KEY Einschränkungen verwendet, um als eindeutiger Zeilenbezeichner für die Tabelle zu dienen. Die IDENTITY Eigenschaft kann winzigen, smallint-, int-, decimal(p,0)- oder numeric(p,0)- Spalten zugewiesen werden. 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 den Ausgangswert und den Schrittweitenwert oder keinen von beiden angeben. Wurden Ausgangswert und inkrementeller Wert nicht angegeben, ist der Standardwert (1,1).
seed
Der Wert, der für die erste in die Tabelle geladene Zeile verwendet wird.
increment
Der Schrittweitenwert, der zum Identitätswert der zuvor geladenen Zeile addiert wird.
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.
NULL | NICHT NULL
Gibt an, ob NULL in der Variablen zulässig ist. Der Standardwert lautet NULL.
PRIMÄRSCHLÜSSEL
Eine Einschränkung, die Entitätsintegrität für eine bestimmte Spalte (oder Spalten) durch einen eindeutigen Index erzwingt. Pro Tabelle kann nur eine PRIMARY KEY Einschränkung erstellt werden.
UNIQUE
Eine Einschränkung, die Entitätsintegrität für eine bestimmte Spalte (oder Spalten) durch einen eindeutigen Index bereitstellt. Eine Tabelle kann mehrere UNIQUE Einschränkungen aufweisen.
GRUPPIERT | NICHT GRUPPIERT
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.
CHECK
Eine Einschränkung, die Domänenintegrität erzwingt, indem die möglichen Eingabewerte für eine oder mehrere Spalten beschränkt wird.
logical_expression
Ein logischer Ausdruck, der zurückgibt
TRUEoderFALSE.
<index_option>
Gibt eine oder mehrere Indexoptionen an. Die explizite Erstellung von Indizes für table-Variablen ist nicht möglich, zudem werden für table-Variablen keine Statistiken geführt. SQL Server 2014 (12.x) hat eine Syntax eingeführt, mit der Sie bestimmte Indextypen inline mit der Tabellendefinition erstellen können. Mithilfe dieser Syntax können Sie Indizes für Tabellenvariablen als Teil der Tabellendefinition erstellen. In einigen Fällen kann die Leistung durch die Verwendung temporärer Tabellen verbessert werden, die vollständige Indexunterstützung und Statistiken bieten.
Eine vollständige Beschreibung dieser Optionen finden Sie unter CREATE TABLE.
Tabellenvariablen und Zeilenschätzungen
table-Variablen haben keine Verteilungsstatistiken. In vielen Fällen erstellt der Optimierer einen Abfrageplan für die Annahme, dass die Tabellenvariable null Zeilen oder eine Zeile aufweist. Weitere Informationen finden Sie unter Tabellendatentyp – Beschränkungen und Einschränkungen.
Aus diesem Grund sollten Sie Tabellenvariablen mit Vorsicht verwenden, wenn Sie von einer großen Anzahl von Zeilen (mehr als 100) ausgehen. Erwägen Sie die folgenden Alternativen:
Temporäre Tabellen sind möglicherweise eine bessere Lösung als Tabellenvariablen, wenn es möglich ist, dass die Zeilenanzahl größer ist (größer als 100).
Verwenden Sie für Abfragen, die die Tabellenvariable mit anderen Tabellen verknüpfen, den
RECOMPILEHinweis, wodurch der Optimierer die richtige Kardinalität für die Tabellenvariable verwendet.In der Azure SQL-Datenbank und beginnend mit SQL Server 2019 (15.x) verteilt die Tabellenvariable verzögerte Kompilierungsfunktion Kardinalitätsschätzungen, die auf der Anzahl der tatsächlichen Tabellenvariablenzeilen basieren und eine genauere Zeilenanzahl für die Optimierung des Ausführungsplans bereitstellen. Weitere Informationen finden Sie unter Intelligente Abfrageverarbeitung in SQL-Datenbanken.
Remarks
Variablen werden häufig in einem Batch oder einer Prozedur als Leistungsindikatoren für WHILE, LOOPoder für einen IF...ELSE Block verwendet.
Variablen können nur in Ausdrücken verwendet werden und stellen keinen Ersatz für Objektnamen oder Schlüsselwörter dar. Verwenden Sie EXECUTEzum Erstellen dynamischer SQL-Anweisungen .
Der Gültigkeitsbereich einer lokalen Variablen ist der Batch, in dem sie deklariert ist.
Eine Tabellenvariable ist nicht zwingend speicherresident. Wenn nicht genügend Speicherplatz vorhanden ist, können die Seiten, die einer Tabellenvariablen zugehörig sind, an tempdb gesendet werden.
Sie können in einer Tabellenvariablen einen Inline-Index definieren.
Auf eine Cursorvariable, der aktuell ein Cursor zugewiesen ist, kann in folgenden Anweisungen als Quelle verwiesen werden:
-
CLOSEAussage -
DEALLOCATEAussage -
FETCHAussage -
OPENAussage -
DELETEPositioniert oderUPDATEAnweisung -
SET CURSORVariable-Anweisung (auf der rechten Seite)
Bei all diesen Anweisungen wird von SQL Server ein Fehler ausgelöst, wenn eine Cursorvariable vorhanden ist, auf die verwiesen wird, für die aber aktuell kein Cursor zugeordnet ist. Ist keine Cursorvariable vorhanden, auf die verwiesen wird, wird von SQL Server der gleiche Fehler ausgelöst wie für eine nicht deklarierte Variable eines anderen Typs.
Eine Cursorvariable hat folgende Eigenschaften:
Sie kann das Ziel eines Cursortyps oder einer anderen Cursorvariablen sein. Weitere Informationen finden Sie unter SET @local_variable.
Kann als Ziel eines Ausgabecursorparameters in einer
EXECUTEAnweisung referenziert werden, wenn der Cursorvariable derzeit kein Cursor zugewiesen ist.Sie sollte als Zeiger auf den Cursor verstanden werden.
Examples
Die Codebeispiele in diesem Artikel verwenden die AdventureWorks2022 Datenbank oder AdventureWorksDW2022 die Beispieldatenbank, die Sie auf der Startseite von Microsoft SQL Server-Beispielen und Communityprojekten herunterladen können.
A. Verwenden von DECLARE
Im folgenden Beispiel wird eine lokale Variable mit dem Namen @find verwendet, um Kontaktinformationen für alle Familiennamen abzurufen, die mit Man.
USE AdventureWorks2022;
GO
DECLARE @find AS VARCHAR (30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Man%';
SELECT p.LastName,
p.FirstName,
ph.PhoneNumber
FROM Person.Person AS p
INNER JOIN Person.PersonPhone AS ph
ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;
Hier sehen Sie das Ergebnis.
LastName FirstName Phone
------------------- ----------------------- -------------------------
Manchepalli Ajay 1 (11) 500 555-0174
Manek Parul 1 (11) 500 555-0146
Manzanares Tomas 1 (11) 500 555-0178
B. Verwenden von DECLARE mit zwei Variablen
Im folgenden Beispiel werden die Namen von Vertriebsmitarbeitern von Adventure Works Cycles abgerufen, die in der Vertriebsregion Nordamerika tätig sind und im laufenden Jahr bereits einen Umsatz von mindestens 2.000.000 USD erzielt haben.
USE AdventureWorks2022;
GO
SET NOCOUNT ON;
GO
DECLARE @Group AS NVARCHAR (50), @Sales AS MONEY;
SET @Group = N'North America';
SET @Sales = 2000000;
SET NOCOUNT OFF;
SELECT FirstName,
LastName,
SalesYTD
FROM Sales.vSalesPerson
WHERE TerritoryGroup = @Group
AND SalesYTD >= @Sales;
C. Deklarieren einer Variablen vom Typ "Tabelle"
Im folgenden Beispiel wird eine table Variable erstellt, die die in der OUTPUT Klausel der UPDATE Anweisung angegebenen Werte speichert. Es folgen zwei SELECT-Anweisungen, die die Werte in @MyTableVar und die Ergebnisse des Updatevorgangs in der Employee-Tabelle zurückgeben. Die Ergebnisse in der Spalte INSERTED.ModifiedDate weichen von den Werten in der Spalte ModifiedDate in der Tabelle Employee ab. Der Grund dafür ist, dass der AFTER UPDATE-Trigger, der den Wert von ModifiedDate auf das aktuelle Datum aktualisiert, in der Employee-Tabelle definiert wird. Die von OUTPUT zurückgegebenen Spalten spiegeln jedoch die Daten wider, bevor Trigger ausgelöst werden. Weitere Informationen finden Sie unter OUTPUT-Klausel.
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
OldVacationHours INT,
NewVacationHours INT,
ModifiedDate DATETIME);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.BusinessEntityID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID,
OldVacationHours,
NewVacationHours,
ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID,
VacationHours,
ModifiedDate
FROM HumanResources.Employee;
GO
D. Deklarieren einer Variablen vom Typ "Tabelle" mit Inlineindizes
Im folgenden Beispiel wird die Variable table mit einem gruppierten Inline-Index und zwei nicht gruppierten Inline-Indizes erstellt.
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
PRIMARY KEY CLUSTERED (EmpID),
UNIQUE NONCLUSTERED (EmpID),
INDEX CustomNonClusteredIndex NONCLUSTERED (EmpID));
GO
Die folgende Abfrage gibt Informationen zu den in der vorherigen Abfrage erstellten Indizes zurück.
SELECT * FROM tempdb.sys.indexes
WHERE object_id < 0;
GO
E. Deklarieren einer Variablen vom benutzerdefinierten Tabellentyp
Im folgenden Beispiel wird ein Tabellenwertparameter oder eine Tabellenvariable mit dem Namen @LocationTVP erstellt. Für diesen Schritt ist ein entsprechender benutzerdefinierter Tabellentyp erforderlich, der aufgerufen wird LocationTableType.
Weitere Informationen zum Erstellen eines benutzerdefinierten Tabellentyps finden Sie unter CREATE TYPE. Weitere Informationen zu Tabellenwertparametern finden Sie unter Verwenden von Tabellenwertparametern (Datenbankmodul).
DECLARE @LocationTVP AS LocationTableType;
Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)
F. Verwenden von DECLARE
Im folgenden Beispiel wird eine lokale Variable mit dem Namen @find verwendet, um Kontaktinformationen für alle Familiennamen abzurufen, die mit Walt.
-- Uses AdventureWorks
DECLARE @find AS VARCHAR (30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Walt%';
SELECT LastName,
FirstName,
Phone
FROM DimEmployee
WHERE LastName LIKE @find;
G. Verwenden von DECLARE mit zwei Variablen
Im folgenden Beispiel werden Variablen zum Angeben der Vor- und Familiennamen von Mitarbeitern in der DimEmployee Tabelle verwendet.
DECLARE @lastName AS VARCHAR (30),
@firstName AS VARCHAR (30);
SET @lastName = 'Walt%';
SET @firstName = 'Bryan';
SELECT LastName,
FirstName,
Phone
FROM DimEmployee
WHERE LastName LIKE @lastName
AND FirstName LIKE @firstName;