T-Sql-Cursor wird beim Abrufen nicht ausgeführt

Question

Ich weiß, dass Cursors verpönt sind, und ich versuche, ihre Verwendung so weit wie möglich zu vermeiden, aber es kann einige legitime Gründe geben, sie zu verwenden. Ich habe einen, und ich versuche, zwei Cursor zu verwenden: einen für die Primärtabelle und einen für die Sekundärtabelle. Der Cursor für die Primärtabelle durchläuft die Primärtabelle in einer äußeren Schleife, der Cursor für die Sekundärtabelle durchläuft die Sekundärtabelle in einer inneren Schleife. Das Problem ist, dass der Cursor der Primärtabelle zwar scheinbar vorgeht und den Wert der Primärschlüsselspalte [Fname] in einer lokalen Variablen @Fname speichert, aber er erhält nicht die Zeile für die entsprechende Fremdschlüsselspalte in der Sekundärtabelle. Für die Sekundärtabelle gibt er immer die Zeilen zurück, deren Fremdschlüsselspaltenwert mit dem Primärschlüsselspaltenwert der erste Zeile der Primärtabelle.

Es folgt ein sehr vereinfachtes Beispiel für das, was ich in der echten gespeicherten Prozedur tun möchte. Names ist die primäre Tabelle

SET NOCOUNT ON
DECLARE 
    @Fname varchar(50) -- to hold the fname column value from outer cursor loop
    ,@FK_Fname varchar(50) -- to hold the fname column value from inner cursor loop
    ,@score int
;

--prepare primary table to be iterated in the  outer loop
DECLARE @Names AS Table (Fname varchar(50))
INSERT @Names
    SELECT 'Jim' UNION
    SELECT 'Bob' UNION
    SELECT 'Sam' UNION
    SELECT 'Jo' 

--prepare secondary/detail table to be iterated in the inner loop
DECLARE @Scores AS Table (Fname varchar(50), Score int)
INSERT @Scores
    SELECT 'Jo',1 UNION
    SELECT 'Jo',5 UNION
    SELECT 'Jim',4 UNION
    SELECT 'Bob',10 UNION
    SELECT 'Bob',15 

--cursor to iterate on the primary table in the outer loop
DECLARE curNames CURSOR
FOR SELECT Fname FROM @Names

OPEN curNames
FETCH NEXT FROM curNames INTO @Fname

--cursor to iterate on the secondary table in the inner loop
DECLARE curScores CURSOR
FOR 
    SELECT FName,Score 
    FROM @Scores 
    WHERE Fname = @Fname 
 --*** NOTE: Using the primary table's column value @Fname from the outer loop

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Outer loop @Fname = ' + @Fname

    OPEN curScores
    FETCH NEXT FROM curScores INTO @FK_Fname, @Score

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT ' FK_Fname=' + @FK_Fname + '. Score=' + STR(@Score)
        FETCH NEXT FROM curScores INTO @FK_Fname, @Score
    END
    CLOSE curScores
    FETCH NEXT FROM curNames INTO @Fname
END

DEALLOCATE curScores

CLOSE curNames
DEALLOCATE curNames

Das Ergebnis sieht folgendermaßen aus. Bitte beachten Sie, dass für die äußere Schleife der aktuelle Fname angezeigt wird, aber wenn dieser Fname als @Fname verwendet wird, um die entsprechende Zeile aus der sekundären Tabelle für die nachfolgenden Iterationen abzurufen, werden immer noch die Zeilen angezeigt, die mit der ersten Zeile (Bob) der primären Tabelle übereinstimmen.

Outer loop @Fname = Bob
    FK_Fname=Bob. Score=10
    FK_Fname=Bob. Score=15
Outer loop @Fname = Jim
    FK_Fname=Bob. Score=10
    FK_Fname=Bob. Score=15
Outer loop @Fname = Jo
    FK_Fname=Bob. Score=10
    FK_Fname=Bob. Score=15
Outer loop @Fname = Sam
    FK_Fname=Bob. Score=10
    FK_Fname=Bob. Score=15

Bitte lassen Sie mich wissen, was ich falsch mache. Vielen Dank im Voraus!

Answer 1

Der Wert von @fName wird bei :DECLARE curScores CURSOR und nicht in der Primärschleife ausgewertet. Sie müssen den zweiten Cursor in der primären Schleife deklarieren und dann wieder freigeben.

Answer 2

Dank einiger Hinweise konnte ich die Lösung finden.

Ich musste den sekundären Cursor innerhalb der ersten Schleife DECLARE und DEALLOCATE. Anfangs habe ich das nur ungern gemacht, weil ich dachte, dass das Zuweisen und Freigeben von Ressourcen in der Schleife keine gute Idee ist, aber ich denke, dass es in dieser speziellen Situation keine andere Möglichkeit gibt, dies zu vermeiden. Jetzt sieht der Arbeitscode in etwa so aus:

SET NOCOUNT ON
DECLARE 
    @Fname varchar(50) -- to hold the fname column value from outer cursor loop
    ,@FK_Fname varchar(50) -- to hold the fname column value from inner cursor loop
    ,@score int
;

--prepare primary table to be iterated in the  outer loop
DECLARE @Names AS Table (Fname varchar(50))
INSERT @Names
    SELECT 'Jim' UNION
    SELECT 'Bob' UNION
    SELECT 'Sam' UNION
    SELECT 'Jo' 

--prepare secondary/detail table to be iterated in the inner loop
DECLARE @Scores AS Table (Fname varchar(50), Score int)
INSERT @Scores
    SELECT 'Jo',1 UNION
    SELECT 'Jo',5 UNION
    SELECT 'Jim',4 UNION
    SELECT 'Bob',10 UNION
    SELECT 'Bob',15 

--cursor to iterate on the primary table in the outer loop
DECLARE curNames CURSOR
FOR SELECT Fname FROM @Names

OPEN curNames
FETCH NEXT FROM curNames INTO @Fname

--cursor to iterate on the secondary table in the inner loop
DECLARE curScores CURSOR
FOR 
    SELECT FName,Score 
    FROM @Scores 
    WHERE Fname = @Fname 
 --*** NOTE: Using the primary table's column value @Fname from the outer loop

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Outer loop @Fname = ' + @Fname

    OPEN curScores
    FETCH NEXT FROM curScores INTO @FK_Fname, @Score

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT ' FK_Fname=' + @FK_Fname + '. Score=' + STR(@Score)
        FETCH NEXT FROM curScores INTO @FK_Fname, @Score
    END
    CLOSE curScores
    FETCH NEXT FROM curNames INTO @Fname
END

DEALLOCATE curScores

CLOSE curNames
DEALLOCATE curNames

Und ich erhalte die richtigen Ergebnisse:

Outer loop @Fname = Bob
    FK_Fname=Bob. Score=        10
    FK_Fname=Bob. Score=        15
Outer loop @Fname = Jim
    FK_Fname=Jim. Score=         4
Outer loop @Fname = Jo
    FK_Fname=Jo. Score=         1
    FK_Fname=Jo. Score=         5
Outer loop @Fname = Sam

Answer 3

Ich denke, dass man dies mit temporären Tabellen, die Zeilennummern haben, viel einfacher machen könnte:

create table #temp1
(
 row int identity(1,1)
 , ... 
)

Es sieht wirklich so aus, als würden Sie von SQL verlangen, sich wie eine Sprache zu verhalten, die Schleifen mag. Das tut sie aber nicht. Jedes Mal, wenn ich eine Schleife in SQL schreibe, frage ich mich: Muss das so gemacht werden? In 7/10 der Fälle lautet die Antwort nein, ich kann es stattdessen mit Sets machen.

Answer 4

Ich würde versuchen, die

DECLARE curScores CURSOR
FOR 
    SELECT FName,Score 
    FROM @Scores 
    WHERE Fname = @Fname 

innerhalb des ersten while, da Sie den Cursor nur für den ersten Namenswert deklarieren