wake-up-neo.com

Wie ordne ich einer Variablen ein Auswahlergebnis zu?

Wie speichere ich einen ausgewählten Feldwert in einer Variablen aus einer Abfrage und verwende ihn in einer Aktualisierungsanweisung?

Hier ist meine Vorgehensweise:

Ich schreibe eine gespeicherte SQL Server 2005-T-SQL-Prozedur, die Folgendes ausführt:

  1. ruft eine Liste der Rechnungs-IDs aus der Rechnungstabelle ab und speichert sie in Cursor
  2. Rechnungs-ID vom Cursor abrufen -> Variable tmp_key
  3. foreach tmp_key findet die primäre Kontakt-ID des Rechnungskunden aus der Kundentabelle
  4. aktualisiert den Client-Kontaktschlüssel mit der primären Kontakt-ID
  5. cursor schließen

Hier ist mein Code:

DECLARE @tmp_key int
DECLARE @get_invckey cursor 

set @get_invckey = CURSOR FOR 
    select invckey from tarinvoice where confirmtocntctkey is null and tranno like '%115876'

OPEN @get_invckey 

FETCH NEXT FROM @get_invckey into @tmp_key

WHILE (@@FETCH_STATUS = 0) 
BEGIN 
    SELECT c.PrimaryCntctKey as PrimaryContactKey
    from tarcustomer c, tarinvoice i
    where i.custkey = c.custkey and i.invckey = @tmp_key

    UPDATE tarinvoice set confirmtocntctkey = PrimaryContactKey where invckey = @tmp_key
    FETCH NEXT FROM @get_invckey INTO @tmp_key
END 

CLOSE @get_invckey
DEALLOCATE @get_invckey

Wie speichere ich den PrimaryContactKey und verwende ihn erneut in der set-Klausel der folgenden Update-Anweisung? Erstelle ich eine Cursor-Variable oder nur eine andere lokale Variable mit einem Int-Typ?

tsqlcursor
70
phill
DECLARE @tmp_key int
DECLARE @get_invckey cursor 

SET @get_invckey = CURSOR FOR 
    SELECT invckey FROM tarinvoice WHERE confirmtocntctkey IS NULL AND tranno LIKE '%115876'

OPEN @get_invckey 

FETCH NEXT FROM @get_invckey INTO @tmp_key

DECLARE @PrimaryContactKey int --or whatever datatype it is

WHILE (@@FETCH_STATUS = 0) 
BEGIN 
    SELECT @PrimaryContactKey=c.PrimaryCntctKey
    FROM tarcustomer c, tarinvoice i
    WHERE i.custkey = c.custkey AND i.invckey = @tmp_key

    UPDATE tarinvoice SET confirmtocntctkey = @PrimaryContactKey WHERE invckey = @tmp_key
    FETCH NEXT FROM @get_invckey INTO @tmp_key
END 

CLOSE @get_invckey
DEALLOCATE @get_invckey

EDIT:
Diese Frage hat viel mehr Anklang gefunden, als ich erwartet hätte. Beachten Sie, dass ich in meiner Antwort nicht die Verwendung des Cursors befürworte, sondern vielmehr zeige, wie der Wert basierend auf der Frage zugewiesen wird.

44
squillman

Ich hatte nur das gleiche Problem und ...

declare @userId uniqueidentifier
set @userId = (select top 1 UserId from aspnet_Users)

oder noch kürzer:

declare @userId uniqueidentifier
SELECT TOP 1 @userId = UserId FROM aspnet_Users
92
Pawel G.

Versuche dies

SELECT @PrimaryContactKey = c.PrimaryCntctKey
FROM tarcustomer c, tarinvoice i
WHERE i.custkey = c.custkey 
    AND i.invckey = @tmp_key

UPDATE tarinvoice SET confirmtocntctkey = @PrimaryContactKey 
WHERE invckey = @tmp_key
FETCH NEXT FROM @get_invckey INTO @tmp_key

Sie würden diese Variable außerhalb Ihrer Schleife nur als Standard-TSQL-Variable deklarieren.

Ich sollte auch beachten, dass Sie dies für jede Art von Auswahl in eine Variable tun würden, nicht nur beim Umgang mit Cursorn.

19
TheTXI

Warum brauchst du überhaupt einen Cursor? Ihr gesamtes Codesegment kann dadurch ersetzt werden, was bei einer großen Anzahl von Zeilen viel schneller abläuft.

UPDATE tarinvoice set confirmtocntctkey = PrimaryCntctKey 
FROM tarinvoice INNER JOIN tarcustomer ON tarinvoice.custkey = tarcustomer.custkey
WHERE confirmtocntctkey is null and tranno like '%115876'
14
GilaMonster

Um eine Variable sicher zuzuweisen, müssen Sie die SET-SELECT-Anweisung verwenden:

SET @PrimaryContactKey = (SELECT c.PrimaryCntctKey
    FROM tarcustomer c, tarinvoice i
    WHERE i.custkey = c.custkey 
    AND i.invckey = @tmp_key)

Stellen Sie sicher, dass Sie sowohl eine Anfangs- als auch eine Endeklammer haben!

Es gibt zwei Gründe, warum die SET-SELECT-Version die sicherste Methode ist, eine Variable festzulegen.

1. Das SELECT liefert mehrere Beiträge

Was passiert, wenn die folgende Auswahl zu mehreren Beiträgen führt?

SELECT @PrimaryContactKey = c.PrimaryCntctKey
FROM tarcustomer c, tarinvoice i
WHERE i.custkey = c.custkey 
    AND i.invckey = @tmp_key

@PrimaryContactKey wird der Wert aus dem letzten Beitrag im Ergebnis zugewiesen.

Eigentlich @PrimaryContactKey wird im Ergebnis ein Wert pro Post zugewiesen, der folglich den Wert des letzten Posts enthält, den der SELECT-Befehl verarbeitet hat.

Welcher Beitrag "letzter" ist, wird durch Clustered-Indizes bestimmt. Wenn kein Clustered-Index verwendet wird oder der Primärschlüssel geclustert ist, ist der "letzte" Beitrag der zuletzt hinzugefügte Beitrag. Dieses Verhalten kann im schlimmsten Fall bei jeder Änderung der Indexierung der Tabelle geändert werden.

Mit einer SET-SELECT-Anweisung wird Ihre Variable auf null gesetzt.

2. Das SELECT liefert keine Beiträge

Was passiert bei Verwendung der zweiten Version des Codes, wenn Ihre Auswahl überhaupt kein Ergebnis liefert?

Im Gegensatz zu dem, was Sie vielleicht glauben, wird der Wert der Variablen nicht null sein - es wird es ist der vorherige Wert ! beibehalten

Dies liegt daran, dass SQL, wie oben erwähnt, der Variablen einen Wert zuweist einmal pro Post - was bedeutet, dass es mit der Variablen nichts zu tun hat, wenn das Ergebnis keine Posts enthält. Die Variable hat also immer noch den Wert, den sie hatte, bevor Sie die Anweisung ausgeführt haben.

Mit der SET-SELECT-Anweisung lautet der Wert null.

Siehe auch: SET versus SELECT beim Zuweisen von Variablen?

11
Erk