SET @local_variable (Transact-SQL)

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-Endpunkt in Microsoft FabricWarehouse in Microsoft Fabric

Legt die angegebene lokale Variable, die zuvor mit der Anweisung DECLARE @local_variable erstellt wurde, auf den angegebenen Wert fest.

Transact-SQL-Syntaxkonventionen

Syntax

Syntax für SQL Server, Azure SQL Database und Azure SQL Managed Instance:

SET   
{ @local_variable  
    [ . { property_name | field_name } ] = { expression | udt_name { . | :: } method_name }  
}  
|  
{ @SQLCLR_local_variable.mutator_method  
}  
|  
{ @local_variable  
    {+= | -= | *= | /= | %= | &= | ^= | |= } expression  
}  
|   
  { @cursor_variable =   
    { @cursor_variable | cursor_name   
    | { CURSOR [ FORWARD_ONLY | SCROLL ]   
        [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]   
        [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]   
        [ TYPE_WARNING ]   
    FOR select_statement   
        [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]   
      }   
    }  
}

Syntax für Azure Synapse Analytics, Parallel Data Warehouse und Microsoft Fabric:

SET @local_variable { = | += | -= | *= | /= | %= | &= | ^= | |= } expression

Hinweis

Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 oder früher finden Sie unter Dokumentation zu früheren Versionen.

Argumente

@local_variable
Der Name einer Variablen eines beliebigen Typs mit Ausnahme von cursor, text, ntext, image oder table. Variablennamen müssen mit einem at-Zeichen ( @ ) beginnen. Variablennamen müssen die Regeln für Bezeichner erfüllen.

property_name
Eigenschaft eines benutzerdefinierten Typs.

field_name
Öffentliches Feld eines benutzerdefinierten Typs.

udt_name
Der Name eines benutzerdefinierten CLR-Typs (Common Language Runtime).

{ . | :: }
Gibt eine Methode für einen benutzerdefinierten CLR-Typ an. Verwenden Sie bei einer Instanzmethode (nicht statisch) einen Punkt (.). Verwenden Sie bei einer statischen Methode zwei Doppelpunkte(::). Zum Aufrufen einer Methode, Eigenschaft oder eines Felds eines CLR-benutzerdefinierten Typs müssen Sie über die EXECUTE-Berechtigung für den Typ verfügen.

Methodenname(Argument [ ,... n] )
Methode eines benutzerdefinierten Typs, die ein oder mehrere Argumente umfassen kann, um den Status einer Instanz eines Typs zu ändern. Statische Methoden müssen öffentlich sein.

@SQLCLR_lokale_Variable
Variable, deren Typ sich in einer Assembly befindet. Weitere Informationen finden Sie unter Common Language Runtime (CLR); Programmierkonzepte für die Integration.

mutator_method
Methode in der Assembly, die den Status des Objekts ändern kann. SQLMethodAttribute.IsMutator wird für diese Methode angewendet.

{ += | -= | *= | /= | %= | &= | ^= | |= }
Verbundzuweisungsoperator:

+= Addition und Zuweisung

-= Subtraktion und Zuweisung

*= Multiplikation und Zuweisung

/= Division und Zuweisung

%= Modulo und Zuweisung

&= Bitweises AND und Zuweisung

^= Bitweises XOR und Zuweisung

|= Bitweises OR und Zuweisung

expression
Beliebiger gültiger Ausdruck.

cursor_variable
Der Name einer Cursorvariablen. Falls die Zielcursorvariable zuvor auf einen anderen Cursor verwiesen hat, wird dieser Verweis entfernt.

cursor_name
Name eines Cursors, der mit der DECLARE CURSOR-Anweisung deklariert wurde.

CURSOR
Gibt an, dass die SET-Anweisung eine Cursordeklaration enthält.

SCROLL
Gibt an, dass der Cursor alle FETCH-Optionen unterstützt (FIRST, LAST, NEXT, PRIOR, RELATIVE und ABSOLUTE). Es kann nur eine der beiden Optionen FAST_FORWARD oder SCROLL angegeben werden.

FORWARD_ONLY
Gibt an, dass der Cursor nur die Option FETCH NEXT unterstützt. Der Cursor wird nur in einer Richtung abgerufen, von der ersten zur letzten Zeile. Wenn Sie FORWARD_ONLY ohne die Schlüsselwörter STATIC, KEYSET oder DYNAMIC angeben, wird der Cursor mit der Option DYNAMIC implementiert. Wenn Sie weder FORWARD_ONLY noch SCROLL angeben, wird standardmäßig FORWARD_ONLY verwendet, es sei denn, Sie geben die Schlüsselwörter STATIC, KEYSET oder DYNAMIC an. STATIC-, KEYSET- und DYNAMIC-Cursor werden standardmäßig auf SCROLL festgelegt.

STATIC
Definiert einen Cursor, der eine temporäre Kopie der von ihm zu verwendenden Daten erzeugt. Alle Anforderungen an den Cursor werden von dieser temporären Tabelle in „tempdb“ beantwortet. Daher werden nach der Öffnung des Cursors die an den Basistabellen vorgenommenen Änderungen in den Daten, die durch Abrufvorgänge an den Cursor zurückgegeben werden, nicht reflektiert. Außerdem unterstützt dieser Cursor keine Änderungen.

KEYSET
Gibt an, dass im Cursor die Mitgliedschaft und Reihenfolge der Zeilen fest ist, wenn der Cursor geöffnet wird. Die Menge der Schlüssel, welche die Zeilen eindeutig identifizieren, wird in der „keyset“-Tabelle in „tempdb“ erstellt. Änderungen an Nichtschlüsselwerten in den Basistabellen, die vom Cursorbesitzer oder durch Ausführen eines Commits von anderen Benutzern vorgenommen wurden, werden sichtbar, wenn der Cursorbesitzer im Cursor einen Bildlauf durchführt. Von anderen Benutzern vorgenommene Einfügungen sind nicht sichtbar, und Einfügungen können nicht über einen Transact-SQL-Server-Cursor vorgenommen werden.

Wenn eine Zeile gelöscht wird, wird bei einem Versuch, die Zeile abzurufen, @@FETCH_STATUS mit dem Wert -2 zurückgegeben. Updates von Schlüsselwerten außerhalb des Cursors sind vergleichbar mit dem Löschen der alten Zeile und dem anschließenden Einfügen der neuen Zeile. Die Zeile mit den neuen Werten ist nicht sichtbar, und der Versuch, die Zeile mit den alten Werten zu holen, liefert einen @@FETCH_STATUS von -2. Die neuen Werte sind sichtbar, wenn das Update über den Cursor durch Angeben der WHERE CURRENT OF-Klausel vorgenommen wird.

DYNAMIC
Definiert einen Cursor, der alle in den Zeilen vorgenommenen Datenänderungen in seinem Resultset widerspiegelt, wenn der Cursorbesitzer im Cursor einen Bildlauf durchführt. Datenwerte, Reihenfolge und Mitgliedschaft der Zeilen können sich bei jedem Abrufvorgang ändern. Die Abrufoptionen FETCH RELATIVE und FETCH ABSOLUTE werden mit dynamischen Cursorn nicht unterstützt.

FAST_FORWARD
Gibt einen FORWARD_ONLY-, READ_ONLY-Cursor mit aktivierter Optimierung an. Es kann nur eine der beiden Optionen FAST_FORWARD oder SCROLL angegeben werden.

READ_ONLY
Verhindert, dass Updates über diesen Cursor erfolgen. Auf den Cursor kann nicht in einer WHERE CURRENT OF-Klausel in einer UPDATE- oder DELETE-Anweisung verwiesen werden. Diese Option überschreibt die Standardeinstellung, nach der ein Cursor aktualisiert werden kann.

SCROLL LOCKS
Gibt an, dass positionierte Updates oder Löschungen durch den Cursor garantiert erfolgreich sind. SQL Server sperrt die Zeilen, während sie in den Cursor eingelesen werden, um ihre Verfügbarkeit für spätere Änderungen sicherzustellen. Es kann nur eine der beiden Optionen SCROLL_LOCKS oder FAST_FORWARD angegeben werden.

OPTIMISTIC
Gibt an, dass positionierte Updates oder Löschungen durch den Cursor nicht erfolgreich sind, wenn die Zeile seit dem letzten Einlesen in den Cursor aktualisiert wurde. SQL Server sperrt keine Zeilen, während sie in den Cursor eingelesen werden. Stattdessen wird durch Vergleiche von timestamp-Spaltenwerten (oder durch einen Prüfsummenwert, wenn die Tabelle keine timestamp-Spalte aufweist) bestimmt, ob die Zeile nach dem Einlesen in den Cursor geändert wurde. Wurde die Zeile geändert, so schlägt der versuchte positionierte Update- oder Löschvorgang fehl. Es kann nur eine der beiden Optionen OPTIMISTIC oder FAST_FORWARD angegeben werden.

TYPE_WARNING
Gibt an, dass dem Client eine Warnmeldung gesendet wird, wenn der Cursor vom angeforderten Typ in einen anderen Typ implizit konvertiert wird.

FOR select_statement
Eine standardmäßige SELECT-Anweisung, die das Resultset des Cursors definiert. Die Schlüsselwörter FOR BROWSE und INTO sind in select_statement einer Cursordeklaration nicht zulässig.

Wenn Sie DISTINCT, UNION, GROUP BY oder HAVING verwenden oder einen Aggregatausdruck in select_list einschließen, wird der Cursor mit der Option STATIC erstellt.

Wenn die zugrunde liegende Tabelle keinen eindeutigen Index und einen ISO SCROLL Cursor hat oder wenn ein Transact-SQL KEYSET Cursor angefordert wird, ist der Cursor automatisch ein STATIC Cursor.

Wenn select_statement eine ORDER BY-Klausel enthält, in der die Spalten keine eindeutigen Zeilenbezeichner sind, wird ein DYNAMIC-Cursor in einen KEYSET-Cursor oder – falls ein KEYSET-Cursor nicht geöffnet werden kann – in einen STATIC-Cursor konvertiert. Ebenso wird mit einem Cursor verfahren, der mithilfe der ISO-Syntax, jedoch ohne das STATIC-Schlüsselwort, definiert wurde.

READ ONLY
Verhindert, dass Updates über diesen Cursor erfolgen. Auf den Cursor kann nicht in einer WHERE CURRENT OF-Klausel in einer UPDATE- oder DELETE-Anweisung verwiesen werden. Diese Option überschreibt die Standardeinstellung, nach der ein Cursor aktualisiert werden kann. Dieses Schlüsselwort unterscheidet sich vom vorherigen READ_ONLY durch das Leerzeichen zwischen READ und ONLY anstelle eines Unterstriches.

UPDATE [OF column_name[ ,... n ] ]
Definiert aktualisierbare Spalten innerhalb des Cursors. Wenn OF column_name [,...n] angegeben wird, können nur in den aufgeführten Spalten Änderungen vorgenommen werden. Wenn keine Liste angegeben wird, können alle Spalten aktualisiert werden, es sei denn, der Cursor wurde mit der Option READ ONLY definiert.

Bemerkungen

Nach dem Deklarieren einer Variablen wird diese auf NULL initialisiert. Verwenden Sie die SET-Anweisung, um einer deklarierten Variablen einen anderen Wert als NULL zuzuweisen. Die SET-Anweisung, die der Variablen einen Wert zuweist, gibt einen einzelnen Wert zurück. Wenn Sie mehrere Variablen initialisieren, verwenden Sie eine separate SET-Anweisung für jede lokale Variable.

Sie können Variablen nur in Ausdrücken verwenden, nicht anstelle von Objektnamen oder Schlüsselwörtern. Um dynamische TransactSQL-Anweisungen zu erstellen, verwenden Sie EXECUTE.

Die Syntaxregeln für SET @cursor_variable schließen die Schlüsselwörter LOCAL und GLOBAL nicht ein. Wenn Sie die Syntax SET @cursor_variable = CURSOR... verwenden, wird der Cursor je nach Einstellung der Datenbankoption „default to local cursor“ als GLOBAL oder LOCAL erstellt.

Cursorvariablen sind stets lokal, selbst wenn sie auf einen globalen Cursor verweisen. Wenn eine Cursorvariable auf einen globalen Cursor verweist, besitzt der Cursor einen globalen und einen lokalen Verweis. Weitere Informationen finden Sie unter Beispiel D, Verwenden von SET mit einem globalen Cursor.

Weitere Informationen finden Sie unter DECLARE CURSOR (Transact-SQL).

Sie können den Verbundzuweisungsoperator stets verwenden, wenn auf der rechten Seite des Operators eine Zuweisung mit einem Ausdruck steht (einschließlich Variablen), und SET in einer UPDATE-, SELECT- oder RECEIVE-Anweisung vorhanden ist.

Verwenden Sie keine Variable in einer SELECT-Anweisung, um Werte zu verketten (d. h., um Aggregatwerte zu berechnen). Es kann zu unerwarteten Abfrageergebnissen kommen, da nicht unbedingt alle Ausdrücke in der SELECT-Liste (einschließlich Zuweisungen) genau einmal für jede Ausgabezeile ausgeführt werden. Weitere Informationen finden Sie in diesem KB-Artikel.

Berechtigungen

Erfordert die Mitgliedschaft in der public-Rolle. Alle Benutzer können SET @local_variable verwenden.

Beispiele

Die meisten der Beispiele verwenden die AdventureWorks-Beispieldatenbank.

A. Drucken des Werts einer Variablen, die mit SET initialisiert wurde

Im folgenden Beispiel wird die @myvar-Variable erstellt, ein Zeichenfolgenwert in die Variable aufgenommen und der Wert der @myvar-Variablen ausgedruckt.

DECLARE @myvar CHAR(20);  
SET @myvar = 'This is a test';  
SELECT @myvar;  
GO

B. Verwenden einer lokalen Variablen, der ein Wert mit SET zugewiesen wurde, in einer SELECT-Anweisung

Im folgenden Beispiel wird eine lokale Variable namens @state erstellt, die dann in einer SELECT-Anweisung verwendet wird, um die Vor- und Nachnamen aller Mitarbeiter zu suchen, die im US-Bundesstaat Oregon ansässig sind.

USE AdventureWorks2022;  
GO  
DECLARE @state CHAR(25);  
SET @state = N'Oregon';  
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name, City  
FROM HumanResources.vEmployee  
WHERE StateProvinceName = @state;
GO

C. Verwenden einer Verbundzuweisung für eine lokale Variable

Mit den beiden folgenden Beispielen wird das gleiche Ergebnis erzielt. Es wird jeweils eine lokale Variable namens @NewBalance erstellt, diese wird mit 10 multipliziert, und der neue Wert der lokalen Variablen wird in einer SELECT-Anweisung angezeigt. Im zweiten Beispiel wird ein Verbundzuweisungsoperator verwendet.

/* Example one */  
DECLARE  @NewBalance  INT ;  
SET  @NewBalance  =  10;  
SET  @NewBalance  =  @NewBalance  *  10;  
SELECT  @NewBalance;
GO
  
/* Example Two */  
DECLARE @NewBalance INT = 10;  
SET @NewBalance *= 10;  
SELECT @NewBalance;
GO

D: Verwenden von SET mit einem globalen Cursor

Im folgenden Beispiel wird eine lokale Variable erstellt und anschließend für die Cursorvariable der globale Cursorname festgelegt.

DECLARE my_cursor CURSOR GLOBAL   
FOR SELECT * FROM Purchasing.ShipMethod  
DECLARE @my_variable CURSOR ;  
SET @my_variable = my_cursor ;   
--There is a GLOBAL cursor declared(my_cursor) and a LOCAL variable  
--(@my_variable) set to the my_cursor cursor.  

DEALLOCATE my_cursor;   
GO
--There is now only a LOCAL variable reference  
--(@my_variable) to the my_cursor cursor.

E. Definieren eines Cursors mit SET

Dieses Beispiel verwendet die SET-Anweisung, um einen Cursor zu definieren.

DECLARE @CursorVar CURSOR;  
  
SET @CursorVar = CURSOR SCROLL DYNAMIC  
FOR  
SELECT LastName, FirstName  
FROM AdventureWorks2022.HumanResources.vEmployee  
WHERE LastName like 'B%';  
  
OPEN @CursorVar;  
  
FETCH NEXT FROM @CursorVar;  
WHILE @@FETCH_STATUS = 0  
BEGIN  
    FETCH NEXT FROM @CursorVar  
END;  
  
CLOSE @CursorVar;  
DEALLOCATE @CursorVar;
GO

F. Zuweisen eines Werts aus einer Abfrage

Das folgende Beispiel verwendet eine Abfrage, um einer Variablen einen Wert zuzuweisen.

USE AdventureWorks2022;  
GO  
DECLARE @rows INT;  
SET @rows = (SELECT COUNT(*) FROM Sales.Customer);  
SELECT @rows;
GO

G. Zuweisen eines Werts zu einer benutzerdefinierten Typvariablen durch Ändern einer Eigenschaft des Typs

Im folgenden Beispiel wird ein Wert für den benutzerdefinierten Typ Point festgelegt, indem der Wert der X-Eigenschaft des Typs geändert wird.

DECLARE @p Point;  
SET @p.X = @p.X + 1.1;  
SELECT @p;  
GO

Erfahren Sie mehr über das Erstellen des Point UDT-Verweises in diesem Beispiel und die folgenden Beispiele im Artikel Erstellen User-Defined Typen.

H. Zuweisen eines Werts zu einer benutzerdefinierten Typvariablen durch Aufrufen einer Methode des Typs

Im folgenden Beispiel wird ein Wert für den benutzerdefinierten Typ point durch Aufrufen der SetXY-Methode des Typs festgelegt.

DECLARE @p Point;  
SET @p=point.SetXY(23.5, 23.5);

I. Erstellen einer Variablen für einen CLR-Typ und Aufrufen einer Mutatormethode

Im folgenden Beispiel wird eine Variable für den Typ Point erstellt und anschließend eine Mutatormethode in Point ausgeführt.

CREATE ASSEMBLY mytest FROM 'c:\test.dll' WITH PERMISSION_SET = SAFE  
CREATE TYPE Point EXTERNAL NAME mytest.Point  
GO  
DECLARE @p Point = CONVERT(Point, '')  
SET @p.SetXY(22, 23);

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

J. Drucken des Werts einer Variablen, die mit SET initialisiert wurde

Im folgenden Beispiel wird die @myvar-Variable erstellt, ein Zeichenfolgenwert in die Variable aufgenommen und der Wert der @myvar-Variablen ausgedruckt.

DECLARE @myvar CHAR(20);  
SET @myvar = 'This is a test';  
SELECT TOP 1 @myvar FROM sys.databases;

K. Verwenden einer lokalen Variablen, der ein Wert mit SET zugewiesen wurde, in einer SELECT-Anweisung

Im folgenden Beispiel wird eine lokale Variable namens @dept erstellt, die dann in einer SELECT-Anweisung verwendet wird, um die Vor- und Nachnamen aller Mitarbeiter zu suchen, die in der Abteilung Marketing arbeiten.

-- Uses AdventureWorks 
  
DECLARE @dept CHAR(25);  
SET @dept = N'Marketing';  
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name  
FROM DimEmployee   
WHERE DepartmentName = @dept;

L. Verwenden einer Verbundzuweisung für eine lokale Variable

Mit den beiden folgenden Beispielen wird das gleiche Ergebnis erzielt. Es wird jeweils eine lokale Variable mit dem Namen @NewBalance erstellt, diese wird mit 10 multipliziert, und der neue Wert der lokalen Variablen wird in einer SELECT-Anweisung angezeigt. Im zweiten Beispiel wird ein Verbundzuweisungsoperator verwendet.

/* Example one */  
DECLARE  @NewBalance INT;  
SET  @NewBalance  =  10;  
SET  @NewBalance  =  @NewBalance  *  10;  
SELECT TOP 1 @NewBalance FROM sys.tables;  
  
/* Example Two */  
DECLARE @NewBalance INT = 10;  
SET @NewBalance *= 10;  
SELECT TOP 1 @NewBalance FROM sys.tables;

M. Zuweisen eines Werts aus einer Abfrage

Das folgende Beispiel verwendet eine Abfrage, um einer Variablen einen Wert zuzuweisen.

-- Uses AdventureWorks 
  
DECLARE @rows INT;  
SET @rows = (SELECT COUNT(*) FROM dbo.DimCustomer);  
SELECT TOP 1 @rows FROM sys.tables;

Nächste Schritte

Weitere Informationen zu verwandten Konzepten finden Sie in den folgenden Artikeln: