DECLARE @local_variable (Transact-SQL)

Artikel
05/23/2023

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric

Variablen werden im Hauptteil eines Batchs oder einer Prozedur mit einer DECLARE-Anweisung deklariert. Die Werte werden mithilfe einer SET- oder SELECT-Anweisung zugewiesen. Cursorvariablen können mit dieser Anweisung deklariert und mit anderen cursorspezifischen Anweisungen verwendet werden. Nach der Deklaration werden alle Variablen mit NULL initialisiert, es sei denn, ein Wert wurde als Teil der Deklaration angegeben.

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 }
} [ ,...n ]
| { @table_variable_name [AS] <table_type_definition> }

<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 ]

Argumente

@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 (Transact-SQL). Weitere Informationen zu benutzerdefinierten CLR-Typen oder Aliasdatentypen finden Sie unter CREATE TYPE (Transact-SQL).

= value
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 (Transact-SQL).

@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, NULL und CHECK. 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 in CREATE TABLE zum Definieren einer Tabelle verwendet werden. Darin sind Elemente und wichtige Definitionen eingeschlossen. Weitere Informationen finden Sie unter CREATE TABLE (Transact-SQL&).

n
Ein Platzhalter, der angibt, dass mehrere Variablen angegeben und ihnen Werte zugewiesen werden können. Beim Deklarieren von table-Variablen muss die table-Variable die einzige Variable sein, die in der DECLARE-Anweisung 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 mithilfe anderer Spalten derselben Tabelle mit einem Ausdruck berechnet. Eine berechnete Spalte kann z. B. folgende Definition haben: cost AS price × qty. Der Ausdruck kann der Name einer nicht berechneten Spalte, eine Konstante, eine integrierte Funktion, eine Variable oder eine beliebige durch einen oder mehrere Operatoren verbundene Kombination der genannten Möglichkeiten sein. 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 Windows- und SQL-Sortierungsnamen finden Sie unter COLLATE (Transact-SQL).

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 der als timestamp definierten Spalten sowie von Spalten mit der IDENTITY-Eigenschaft. DEFAULT-Definitionen werden entfernt, wenn die Tabelle gelöscht wird. Es kann nur ein konstanter Wert wie eine Zeichenfolge, eine Systemfunktion, z. B. SYSTEM_USER(), oder NULL als Standardwert verwendet werden. Um die Kompatibilität mit früheren Versionen von SQL Server aufrechtzuerhalten, ist es möglich, einer DEFAULT-Definition einen Einschränkungsnamen zuzuweisen.

constant_expression
Eine Konstante, ein NULL-Wert oder eine Systemfunktion, die bzw. der 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 üblicherweise zusammen mit PRIMARY KEY-Einschränkungen verwendet, um als eindeutiger Zeilenbezeichner für die Tabelle zu dienen. Die IDENTITY-Eigenschaft kann folgenden Spalten zugewiesen werden: tinyint, smallint, int, 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 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. Nur eine uniqueidentifier-Spalte pro Tabelle kann als ROWGUIDCOL-Spalte gekennzeichnet werden. Die ROWGUIDCOL-Eigenschaft kann nur einer uniqueidentifier-Spalte zugewiesen werden.

NULL | NOT NULL

Gibt an, ob NULL in der Variablen zulässig ist. Der Standardwert ist NULL.

PRIMARY KEY

Eine Einschränkung, die Entitätsintegrität für eine bestimmte Spalte (oder Spalten) durch einen eindeutigen Index erzwingt. Es kann nur eine PRIMARY KEY-Einschränkung pro Tabelle 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 haben.

CLUSTERED | NONCLUSTERED

Gibt an, dass ein gruppierter oder nicht gruppierter Index für die PRIMARY KEY- oder UNIQUE-Einschränkung erstellt wird. PRIMARY KEY-Einschränkungen verwenden CLUSTERED, und UNIQUE-Einschränkungen verwenden NONCLUSTERED.

CLUSTERED kann nur für eine einzelne Einschränkung angegeben werden. Wenn neben CLUSTERED für eine UNIQUE-Einschränkung auch eine PRIMARY KEY-Einschränkung angegeben wird, verwendet die Einschränkung PRIMARY KEY den Wert 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 TRUE oder FALSE zurückgibt.

<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. Mit SQL Server 2014 (12.x) wurde eine neue Syntax eingeführt, die es erlaubt, bestimmte Indextypen inline mit der Tabellendefinition zu erstellen. Mit dieser neuen Syntax können Sie Indizes für table-Variablen als Teil der Tabellendefinition erstellen. In einigen Fällen kann die Leistung verbessert werden, indem stattdessen temporäre Tabellen verwendet werden, die eine vollständige Unterstützung für Indizes und Statistiken bieten.

Eine vollständige Beschreibung dieser Optionen finden Sie unter CREATE TABLE.

Tabellenvariablen und Zeilenschätzungen

table-Variablen haben keine Verteilungsstatistiken. Daher erstellt der Optimierer in vielen Fällen einen Abfrageplan unter der Annahme, dass die „table“-Variable Null oder eine Zeile enthält. 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 können eine bessere Lösung sein als Tabellenvariablen, wenn die Zeilenzahl größer sein kann (mehr als 100).
Verwenden Sie bei Abfragen, die einen Join der Tabelle mit anderen Tabellen ausführen, auch den RECOMPILE-Hinweis. Dieser führt dazu, dass der Optimierer die korrekte Kardinalität für die „table“-Variable verwendet.
In Azure SQL-Datenbank und ab SQL Server 2019 (15.x) überträgt das Feature für die verzögerte Kompilierung von Tabellenvariablen Kardinalitätsschätzungen, die auf der tatsächlichen Zeilenzahl von Tabellenvariablen basieren, und liefert so eine genauere Zeilenzahl für die Optimierung des Ausführungsplans. Weitere Informationen finden Sie unter Intelligente Abfrageverarbeitung in SQL-Datenbanken.

Bemerkungen

Variablen werden oft in einem Batch oder einer Prozedur als Zähler für WHILE, LOOP oder für IF...ELSE-Blöcke verwendet.

Variablen können nur in Ausdrücken verwendet werden und stellen keinen Ersatz für Objektnamen oder Schlüsselwörter dar. Um dynamische SQL-Anweisungen zu erstellen, verwenden Sie EXECUTE.

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:

CLOSE-Anweisung
DEALLOCATE-Anweisung
FETCH-Anweisung
OPEN-Anweisung
Positionierte DELETE- oder UPDATE-Anweisung
SET CURSOR VARIABLE-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 (Transact-SQL).
Auf sie kann als Ziel eines Ausgabecursorparameters in einer EXECUTE-Anweisung verwiesen werden, wenn der Cursorvariablen aktuell kein Cursor zugewiesen ist.
Sie sollte als Zeiger auf den Cursor verstanden werden.

Beispiele

A. Verwenden von DECLARE

Im folgenden Beispiel werden mithilfe der lokalen Variablen @find Kontaktinformationen für alle Nachnamen abgerufen, die mit Man beginnen.

USE AdventureWorks2022;
GO
DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Man%';
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
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

(3 row(s) affected)

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 nvarchar(50), @Sales 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 "table"

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 (Transact-SQL).

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 des Typs „Tabelle“ mit Inline-Indizes

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 Typ 'user-defined table'

Im folgenden Beispiel wird ein Tabellenwertparameter oder eine Tabellenvariable mit dem Namen @LocationTVP erstellt. Dies erfordert einen entsprechenden benutzerdefinierten Tabellentyp mit dem Namen LocationTableType. Weitere Informationen zum Erstellen eines benutzerdefinierten Tabellentyps finden Sie unter CREATE TYPE (Transact-SQL). Weitere Informationen zu Tabellenwertparametern finden Sie unter Verwenden von Tabellenwertparametern (Datenbank-Engine).

DECLARE @LocationTVP
AS LocationTableType;

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)

F. Verwenden von DECLARE

Im folgenden Beispiel werden mithilfe der lokalen Variablen @find Kontaktinformationen für alle Nachnamen abgerufen, die mit Walt beginnen.

-- Uses AdventureWorks

DECLARE @find 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

Das folgende Beispiel ruft Variablen ab, um die Vor- und Nachnamen von Mitarbeitern in der DimEmployee-Tabelle anzugeben.

-- Uses AdventureWorks

DECLARE @lastName VARCHAR(30), @firstName VARCHAR(30);

SET @lastName = 'Walt%';
SET @firstName = 'Bryan';

SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @lastName AND FirstName LIKE @firstName;

Teilen über