DECLARE @local_variable (Transact-SQL)

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

DECLARE 
     { 
{{ @local_variable [AS] data_type } | [ = value ] }
    | { @cursor_variable_name CURSOR }
} [,...n] 
    | { @table_variable_name [AS] <table_type_definition> | <user-defined table type> } 

<table_type_definition> ::= 
     TABLE ( { <column_definition> | <table_constraint> } [ ,... ] 
      ) 

<column_definition> ::= 
          column_name { scalar_data_type | AS computed_column_expression }
     [ COLLATE collation_name ] 
     [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed ,increment ) ] ] 
     [ ROWGUIDCOL ] 
     [ <column_constraint> ] 

<column_constraint> ::= 
     { [ NULL | NOT NULL ] 
     | [ PRIMARY KEY | UNIQUE ] 
     | CHECK ( logical_expression ) 
     } 

<table_constraint> ::= 
     { { PRIMARY KEY | UNIQUE } ( column_name [ ,... ] ) 
     | CHECK ( search_condition ) 
     } 

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 vom System bereitgestellter, CLR-benutzerdefinierter Tabellentyp (Common Language Runtime) oder Aliasdatentyp. Eine Variable kann nicht vom Datentyp text, ntext oder image sein.

  Weitere Informationen zu Systemdatentypen finden Sie unter Datentypen (Transact-SQL). Weitere Informationen zu CLR-benutzerdefinierten 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.

• @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 vom Typ table. Variablennamen müssen mit einem at-Zeichen (@) beginnen und den Regeln für Bezeichner entsprechen.

• <table_type_definition>
  Definiert den table-Datentyp. 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 Skalar-Spaltendatentyp verwendet werden, wenn eine Regel oder Standarddefinition an den Typ gebunden ist.

  <table_type_definition> ist eine Teilmenge von Informationen, die zum Definieren einer Tabelle in CREATE TABLE 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 beispielsweise die Definition cost AS price * qty aufweisen. 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 CLR-benutzerdefinierten Typ verweisen.

• [ COLLATE collation_name]
  Gibt die Sortierung für die Spalte an. collation_name kann entweder der Name einer Windows-Sortierreihenfolge oder ein SQL-Sortierungsname sein und gilt nur für Spalten der Datentypen char, varchar, text, nchar, nvarchar und ntext. 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-Sortierreihenfolgen und zu den 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 zu gewährleisten, kann einem DEFAULT-Wert ein Einschränkungsname zugewiesen werden.

• 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 in der Regel in Verbindung mit PRIMARY KEY-Einschränkungen verwendet, um als eindeutiger Zeilenbezeichner für die Tabelle zu dienen. Die IDENTITY-Eigenschaft kann tinyint-, 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. 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
  Schlüsselwörter, die bestimmen, ob NULL-Werte in der Spalte zulässig sind.

• 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.

• CHECK
  Eine Einschränkung, die Domänenintegrität erzwingt, indem die möglichen Eingabewerte für eine oder mehrere Spalten beschränkt werden.

• logical_expression
  Ein logischer Ausdruck, der TRUE oder FALSE zurückgibt.

• <<user-defined table type>>
  Gibt an, dass die Variable ein benutzerdefinierter Tabellentyp ist.

Hinweise

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, nicht anstelle von Objektnamen oder Schlüsselwörtern. Um dynamische SQL-Anweisungen zu erstellen, verwenden Sie EXECUTE.

Der Gültigkeitsbereich einer lokalen Variablen ist der Batch, in dem sie deklariert ist.

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. Weitere Informationen zu Cursorvariablen finden Sie unter Transact-SQL-Cursor.

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 AdventureWorks;
GO
DECLARE @find varchar(30);
/* Also allowed: 
DECLARE @find varchar(30) = 'Man%';
*/
SET @find = 'Man%';
SELECT LastName, FirstName, Phone
FROM Person.Contact
WHERE LastName LIKE @find;

Dies ist das Resultset.

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 AdventureWorks;
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 Aktualisierungsvorgangs in der Employee-Tabelle zurückgeben. Beachten Sie, dass sich die Ergebnisse in der INSERTED.ModifiedDate-Spalte von den Werten in der ModifiedDate-Spalte in der Employee -Tabelle unterscheiden. 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 AdventureWorks;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
OUTPUT inserted.EmployeeID,
       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.
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

D. 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 Tabellenwertparameter (Datenbankmodul).

DECLARE @LocationTVP 
AS LocationTableType;

Siehe auch

Verweis

EXECUTE (Transact-SQL)

Funktionen (Transact-SQL)

SELECT (Transact-SQL)

table (Transact-SQL)

Konzepte

Typisiertes XML im Vergleich zu nicht typisiertem XML

Ändern benutzerdefinierter Typvariablen und Parameterwerte