Cursor: Ausgabe in 1 Tabelle

Question

Ich brauche eine bestimmte ID (ObjNo) und das Ergebnis einer komplexen SP, das wie eine Tabelle dargestellt werden soll. Die SP liefert jeweils genau einen Datensatz.

Es soll so aussehen:

ObjNo/Ergebnis_Spalte1/Ergebnis_Spalte2/Ergebnis_Spalte3
10001/xxxx/yyyy/zzzz
10002/aaa/bbb/ccc
10003/..........usw.

Ich denke, sowas geht am Besten mit einem Cursor und habe hier nach Beispielen gesucht und dann diesen Code verwendet (es ist mein erster Cursor ;-) ):

DECLARE cur CURSOR FOR
select  Objno from tblAdressen where Objno between 10001 and 19999  order by Objno;
Declare @ObjNo as integer;
OPEN cur;
FETCH NEXT FROM cur;

WHILE @@FETCH_STATUS = 0
   BEGIN
      FETCH NEXT FROM cur into @ObjNo;
      select @ObjNo  as ActNo;      
      exec getCourtAddressTest @ObjNo       
   END;
CLOSE cur;
DEALLOCATE cur;
GO

Das klappt im Prinzip auch, aber die Darstellung sieht so aus:

ObjNo
10001
Ergebnis_Spalte1/Ergebnis_Spalte2/Ergebnis_Spalte3
xxxx/yyyy/zzzz
ObjNo
10002
Ergebnis_Spalte1/Ergebnis_Spalte2/Ergebnis_Spalte3
aaa/bbb/ccc ..... usw.

Wie kann man erstens objNo und das Ergebnis in eine Zeile bringen und zweitens alles wie eine Tabelle aussehen lassen? Wahrscheinlich ist es ganz einfach.

Answer 1

Hallo Klaus Dieter, definiere Dir eine Tabelle oder Tabellenvariable und verwende sie für die Zwischenspeicherung des EXEC.

Declare @Tab as Table(Feldliste);
...
While ...
     Delete from @Tab;
     Insert into @Tab(Feldliste)
     exec getCourtAddressTest @ObjNo             ;
        Select @ObjNo    as ActNo, Feldliste
     from @Tab;
End;

Dann kannst Du das Ergebnis entsprechend ausgeben.

Mehr Infos zu den Möglichkeiten Daten von Stored Procedures zu verarbeiten, hat Erland Sommarskog gesammelt:
http://www.sommarskog.se/share_data.html
 Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 2

Hallo Christoph,

vielen Dank für die Antwort. Das mit der Tabelle ist eine gute Idee. Allerdings weiß ich nicht wie ich die ID @ObjNo  in die Tabelle bekomme. Schließlich ist diese ID die übergebene Variabel in der SP (exec getCourtAddressTest @ObjNo ). Wie fügt man diese gezielt ein?

Ich habe es so versucht :

      DECLARE @Tab as table
           (ObjNO integer, Feld1 varchar(100),  Feld2 varchar(100),   Feld3 varchar(100));  

....

      Delete from @Tab;
      FETCH NEXT FROM cur into @ObjNo;
      insert into @Tab(ObjectNo, Feld1, Feld2, Feld3 )         
      select @ObjNo  as ActNo;      
      exec getCourtAddressTest @ObjNo  ;
      select * from @Tab

Aber dann kommt eine Fehlermeldung wegen falscher Spaltenzahl. Ich muss also irgendwie @ObjNo und das Ergebnis der SP in das Select bekommen.

Answer 3

Hallo Klaus Dieter!
Mein Beispiel war doch eigentlich anders herum, oder?
Daten aus der Prozedur in die Tabelle schieben und danach die Objektnummer mit dem Inhalt der Tabelle zusammen ausgeben.

Bei Deinem ersten Insert gibst Du vier Spalten für das Ziel an, selektierst aber nur einen Wert. Falls der Wert unbedingt in der Tabelle landen muss, reduziere hier die Spaltenliste auf eine Spalte.
Vor Deinem EXEC fehlt das Insert mit der korrekten Spaltenliste für das Ergebnis.
Wenn Du das so abbildest, hast Du nachher mindestens zwei Zeilen. Die erste hätte die Objektnummer gefüllt und die anderen Spalten leer, die nächsten hätten die Objektnummer leer und die anderen Spalten gefüllt. Deshalb habe ich auf die Objektnummer in dieser Tabelle verzichtet.

Du kannst ja am Ende noch das Select mit Objektnummer und den Spalten der Zwischentabelle in eine weitere (permanente) Tabelle übernehmen, wenn Du die Werte in einer Zeile historisiert benötigst.
 Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 4

Hallo Christoph,

nochmals vielen Dank. OK, ich habe mich vielleicht unklar ausgedrückt. Die SP gibt 3 Spalten zurück, die ich hier vereinfacht mit Feld1 bis Feld3 bezeichnet habe.

Ich habe den Code-Ausschnitt von oben leicht verändert (d.h. alles mit @ObjNo rausgenommen):

DECLARE @Tab as table
           (ObjNO integer, Feld1 varchar(100),  Feld2 varchar(100),   Feld3 varchar(100));  

....

      Delete from @Tab;
      FETCH NEXT FROM cur into @ObjNo;
      insert into @Tab( Feld1, Feld2, Feld3 )         
      --select @ObjNo  as ActNo;      
      exec getCourtAddressTest @ObjNo  ;
      select * from @Tab

Dadurch habe ich aber nichts gewonnen, das Ergebnis sieht genauso aus wie in meiner ersten Version: Überschrift, 1. Datensatz, Überschrift, 2. Datensatz usw. Und vor allem habe ich damit natürlich immer noch nicht die Variable @ObjNo  in der Ausgabetabelle.

Answer 5

Klaus Dieter! Das Select-Statement in meinem Beispiel sah so aus:

             Select @ObjNo        as ActNo, Feldliste
        from @Tab;

Damit erhältst Du alle Werte in einem Resultset. Falls Du die Werte in der Tabelle haben möchtest, kannst Du diese ja auch vor Deinem Select * per Update dort einfügen:

             Update    @Tab set ObjNo = @ObjNo;

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 6

Hallo Christoph,

nochmals vielen Dank. OK, ich habe es jetzt so gemacht:

      insert into @Tab( Name1, Strasse, PLZOrt ) 

      exec getCourtAddressTest  @ObjNo ;
      Update  @Tab set ObjNo =  @ObjNo;
      select @objno as ActNo, Name1, Strasse,PLZOrt from @Tab; 

Durch das Update habe ich auch die ActNo in der Ausgabe (und damit bin ich einen großen Schritt weiter). Es gibt aber nach wie vor den Schönheitsfehler, dass keine Tabelle ausgegegeben wird, sonden abwechselnd eine Überschrift und ein Datensatz. Habe ich eventuell, das mit dem Resultset falsch verstanden?

Answer 7

Hallo Klaus Dieter, ich glaube, so langsam verstehe ich das Problem. Versuche es mal mit meinem Beispiel-Skript:

Use tempdb
go
Create procedure getCourtAddressTest    @ObjNo integer
as
     Select 'Name' + CAST(@ObjNo as varchar(2)) as Name1, 'Strasse'+
CAST(@ObjNo as varchar(2)) as Strasse, 'PLZOrt'+ CAST(@ObjNo as varchar(2))
as PLZORT;
Return(0);
go

Declare @ObjNo as integer;
DECLARE @Tab as table (ObjNO integer, Name1 varchar(100),    Strasse
varchar(100),     PLZOrt varchar(100));

Set @ObjNo = 1;
While @ObjNo < 10
begin
     insert into @Tab( Name1, Strasse, PLZOrt )
     exec getCourtAddressTest    @ObjNo ;
        Update    @Tab set ObjNo =    @ObjNo where ObjNO is Null;
        Set @ObjNo = @ObjNo + 1 ;
end;
select ObjNO as ActNo, Name1, Strasse, PLZOrt from @Tab;
go

Drop procedure getCourtAddressTest    ;

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 8

Hallo Christoph,

nochmals recht herzlichen Dank. Aber soweit war ich vor meinem Ausgangsposting hier auch schon. Via Schleife über einen Zähler hätte ich es auch geschafft ohne mich hier an das Forum zu wenden ;-) . Das Problem ist aber, ich will die IDs aus einer Tabelle (bzw. query - diese Nummern sind zudem auch nicht lückenlos) entnehmen. Und wie man Schleifen über selects macht, weiß ich leider nicht. Ich habe nur dunkel in Erinnerung, dass sich dazu ein Cursor anbietet. Meine Erfahrungen mit Cursorn sind allerdings sehr überschaubar.

Viele Grüße Klaus-Dieter

Answer 9

Hallo Klaus Dieter,
ich dachte, die Programmierung mit dem Cursor wäre Dir schon klar gewesen. Mein Beispiel war dazu da, zu verdeutlichen, wie Du alle Werte in einer Tabelle sammelst, damit am Ende nur ein SELECT verwendet werden kann und damit nur eine Überschrift erscheint.
 Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 10

Ach so war das gemeint. OK, ich gehe davon aus, dass Fetch next dem Set @ObjNo = @ObjNo + 1 ; in deinem Beispiel entspricht und habe das Ganze nochmal umgestellt (die alten Anweisungen habe ich einfach nur auskommentiert, damit man den Unterschied zu vorher sieht):

WHILE @@FETCH_STATUS = 0
   BEGIN
      --Delete from @Tab;
      --FETCH NEXT FROM cur into @ObjNo;
      insert into @Tab( Name1, Strasse, PLZOrt )
      exec getCourtAddressTest  @ObjNo ;
      Update  @Tab set ObjNo =  @ObjNo where ObjNo is null;
      --select @objno as ActNo, Name1, Strasse, PLZOrt from @Tab;
       FETCH NEXT FROM cur into @ObjNo;
   END;
     select @objno as ActNo, Name1, Strasse,PLZOrt from @Tab;
CLOSE cur;

OK, wenn man das so macht, bekommt zuerst eine Ausgabe der ersten objNo nebst Überschrift (unnötig, aber natürlich kein Problem). Und danach wird tatsächlich eine wunschgemäße Tabelle ausgegeben. Nur haben wir jetzt noch den Fehler, dass alle ActNos mit der gleichen Nr. (nämlich der letzten) aktualisiert wurden.

Answer 11

Deine Interpretation des Fehlers: "Nur haben wir jetzt noch den Fehler, dass alle ActNos mit der gleichen Nr. (nämlich der letzten) aktualisiert wurden."
ist falsch.
Du gibst ja die Variable aus und nicht die Spalte aus der Tabelle. Das @ ist zu viel. Korrekt wäre:

select objno as ActNo, Name1, Strasse,PLZOrt from @Tab;

 Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 12

Hallo Christoph,

jetzt fällt es mir wie Schuppen aus den Haaren - Kaum mache ich es richtig, geht es auch ;-) Das kommt davon, wenn man etwas von woanders einfach rüberkopiert.

Auf jeden Fall: Du hast mir sehr geholfen (auch für künftige ähnliche Problem). Vielen Dank

Klaus-Dieter