SET @local_variable (Transact-SQL)

  • Artikel

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

Legt die angegebene lokale Variable, die zuvor mithilfe der DECLARE @local_variable Anweisung 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 [ [ LOCAL | GLOBAL ] ]
        [ 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 (12.x) und 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 für eine Instanz (nicht statische) Methode einen Punkt (.). Verwenden Sie für eine statische 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.

method_name ( 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_local_variable

Variable, deren Typ sich in einer Assembly befindet. Weitere Informationen finden Sie unter ClR-Integrationsprogrammierungskonzepte (Common Language Runtime).

mutator_method

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

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

Verbundzuweisungsoperator:

  • += – Hinzufügen und Zuweisen
  • -= - Subtrahieren und Zuweisen
  • *= - Multiplizieren und Zuweisen
  • /= - Dividieren und Zuweisen
  • %= - Modulo und Zuweisen
  • &= - Bitweise AND und Zuweisen
  • ^= - Bitweise XOR und Zuweisen
  • |= - Bitweise OR und Zuweisen

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

Der Name eines Cursors, der mithilfe der DECLARE CURSOR Anweisung deklariert wird.

CURSOR

Gibt an, dass die SET Anweisung eine Deklaration eines Cursors enthält.

SCROLL

Gibt an, dass der Cursor alle Abrufoptionen unterstützt: FIRST, , LAST, NEXT, , PRIOR, und RELATIVEABSOLUTE. Sie können nicht angeben SCROLL , wann Sie auch angeben FAST_FORWARD.

FORWARD_ONLY

Gibt an, dass der Cursor nur die FETCH NEXT Option unterstützt. Der Cursor wird nur in einer Richtung abgerufen, von der ersten zur letzten Zeile. Wenn Sie ohne die , oder Schlüsselwort (keyword)s angebenFORWARD_ONLY, wird der Cursor als DYNAMICimplementiert.DYNAMICKEYSETSTATIC Wenn Sie weder angeben FORWARD_ONLYSCROLLnoch , FORWARD_ONLY ist die Standardeinstellung, es sei denn, Sie geben die Schlüsselwort (keyword)s STATIC, , KEYSEToder DYNAMIC. Für STATIC, KEYSETund DYNAMIC Cursor SCROLL ist die Standardeinstellung.

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 tempdbbeantwortet. 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. Der Satz von Schlüsseln, die die Zeilen eindeutig identifizieren, ist in die Keysettable integriert in tempdb. Ä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 versucht, die Zeile abzurufen, eine @@FETCH_STATUS von -2. 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 versucht, die Zeile mit den alten Werten abzurufen.@@FETCH_STATUS-2 Die neuen Werte sind sichtbar, wenn die Aktualisierung durch den Cursor erfolgt, indem sie die WHERE CURRENT OF Klausel angeben.

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_ONLYCursor READ_ONLY mit aktivierten Optimierungen an. FAST_FORWARD kann nicht angegeben werden, wenn SCROLL auch angegeben wird.

READ_ONLY

Verhindert, dass Updates über diesen Cursor erfolgen. Auf den Cursor kann in einer Klausel in einer WHERE CURRENT OFUPDATE Oder-Anweisung DELETE nicht 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. Sie können nicht angeben SCROLL_LOCKS , wann FAST_FORWARD auch angegeben wird.

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. Sie können nicht angeben OPTIMISTIC , wann FAST_FORWARD auch angegeben wird.

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 Standard-Anweisung SELECT , die den Resultset des Cursors definiert. Die Schlüsselwort (keyword)s FOR BROWSEund INTO sind innerhalb der select_statement einer Cursordeklaration nicht zulässig.

Wenn Sie einen Aggregatausdruck in das select_list verwendenDISTINCTGROUP BYUNION, wird HAVINGder Cursor als .STATIC

Wenn jede zugrunde liegende Tabelle keinen eindeutigen Index und einen ISO-Cursor SCROLL enthält oder wenn ein Transact-SQL-Cursor KEYSET 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 in einen STATIC Cursor konvertiert, wenn ein KEYSET Cursor nicht geöffnet werden kann. Dieser Vorgang tritt auch für einen Cursor auf, der mithilfe der ISO-Syntax definiert wird, jedoch ohne die STATIC Schlüsselwort (keyword).

READ ONLY

Verhindert, dass Updates über diesen Cursor erfolgen. Auf den Cursor kann in einer Klausel in einer WHERE CURRENT OFUPDATE Oder-Anweisung DELETE nicht verwiesen werden. Diese Option überschreibt die Standardeinstellung, nach der ein Cursor aktualisiert werden kann. Dieser Schlüsselwort (keyword) unterscheidet sich von der früherenREAD_ONLY, indem ein Leerzeichen anstelle eines Unterstrichs zwischen READ und ONLY.

UPDATE [ OF column_name [ ,... n ] ]

Definiert aktualisierbare Spalten innerhalb des Cursors. Wenn OF <column_name> [ , ...n ] angegeben wird, können Änderungen nur in den aufgelisteten Spalten vorgenommen werden. Wenn keine Liste angegeben wird, können alle Spalten aktualisiert werden, es sei denn, der Cursor ist definiert als READ_ONLY.

Hinweise

Nachdem eine Variable deklariert wurde, wird sie initialisiert in NULL. Verwenden Sie die SET Anweisung, um einen Wert zuzuweisen, der keiner NULL deklarierten Variablen entspricht. 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. Verwenden Sie EXECUTEzum Erstellen dynamischer Transact-SQL-Anweisungen .

Obwohl Syntaxregeln für SET @cursor_variable die LOCAL Ein- und GLOBAL Schlüsselwort (keyword) enthalten sind, wird der Cursor bei Verwendung der SET @cursor_variable = CURSOR... Syntax je nach Einstellung der Standardoption für die lokale Cursordatenbank erstellt GLOBALLOCALoder verwendet.

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 zusammengesetzten Zuordnungsoperator überall verwenden, wo Sie über eine Zuordnung mit einem Ausdruck auf der rechten Seite des Operators verfügen, einschließlich Variablen und einer SET in einem , SELECTund RECEIVE einer UPDATEAnweisung.

Verwenden Sie keine Variable in einer SELECT Anweisung, um Werte zu verketten (d. h. zum Berechnen von Aggregatwerten). Unerwartete Abfrageergebnisse können auftreten, da alle Ausdrücke in der SELECT Liste (einschließlich Zuweisungen) nicht unbedingt einmal für jede Ausgabezeile ausgeführt werden. Weitere Informationen finden Sie unter KB 287515.

Berechtigungen

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

Beispiele

Dieser Artikel erfordert die AdventureWorks2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

.A Drucken des Werts einer Variablen, die mithilfe von SET initialisiert wird

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, die einem Wert zugewiesen wurde, mithilfe von SET in einer SELECT-Anweisung

Das folgende Beispiel erstellt eine lokale Variable namens @state und verwendet die lokale Variable in einer SELECT Anweisung, um den Vornamen (FirstName) und den Familiennamen (LastName) aller Mitarbeiter zu finden, die im Status von Oregon.

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 zusammengesetzten Zuordnung für eine lokale Variable

Mit den beiden folgenden Beispielen wird das gleiche Ergebnis erzielt. Jedes Beispiel erstellt eine lokale Variable namens @NewBalance, multipliziert sie mit 10, und zeigt dann den neuen Wert der lokalen Variablen in einer SELECT Anweisung an. 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 mithilfe von 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)

Dieser Artikel erfordert die AdventureWorks2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

J. Drucken des Werts einer Variablen, die mithilfe von SET initialisiert wird

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, die einem Wert zugewiesen wurde, mithilfe von SET in einer SELECT-Anweisung

Im folgenden Beispiel wird eine lokale Variable namens erstellt @dept und diese lokale Variable in einer SELECT Anweisung verwendet, um den Vornamen (FirstName) und den Familiennamen (LastName) aller Mitarbeiter zu finden, die in der Marketing Abteilung arbeiten.

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

L. Verwenden einer zusammengesetzten Zuordnung 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;

Zugehöriger Inhalt