none
Stored Procedure über Schleife ausgeben

    Întrebare

  • Ich habe eine SP, die genau einen Wert liefert wobei ein Parameter übergeben wird. Das ist einzeln mit exec sp_xxx 12345 kein Problem. Aber ich möchte die Parameter aus einer Query entnehmen und zudem alle Datensätze dieser Query durchlaufen. Ich habe mal versucht, das über ein Select zu lösen, aber Selects und SPs vertragen sich offensichtlich nicht. Bleibt eine Schleife. Aber wie kann man damit  eine query durchlaufen?

    23 februarie 2012 09:52

Răspunsuri

Toate mesajele

  • Hallo KlausDieter,

    willst Du jetzt in einer SP mehrere Datensätze verarbeiten oder innerhalb einer Abfrage die SP einbinden? Wenn letzteres, wäre eine Skalarfunktion wohl das, was Du suchst. Damit könntest Du dann bspw. sowas machen:

    SELECT Spalte1, Spalte2, dbo.fct_MachWas( <Wert1>, SpalteX ) FROM <Tabelle> ...
    


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

    23 februarie 2012 10:04
  • Hi Stefan,

    so wie du es skiziert hast wäre es perfekt. Ich habe es so versucht

    select top 10 KDID, (dbo.sp_xxx KDID) from testtabelle where KDID >1000

    Nur kommt dann die Fehlermeldung "Incorrect syntax near 'KDID'.


    23 februarie 2012 10:44
  • Hallo KlausDieter,

    a) sp_Irgendwas war noch nie eine gute Benennung für Stored Procedures. (Warum, steht im verlinkten MSDN Artikel)

    b) Ich schrieb, dass Du eine Skalarfunktion brauchst. Mit einer Stored Procedure wird das IMHO nicht funktionieren.

    c) Die Klammern um die Parameterübergabe fehlen.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

    23 februarie 2012 11:50
  • Hallo Stefan,

    nochmals vielen Dank für die Antwort. Also:

    zu a) Selbstverständlich verwende ich "in Natura" sprechende Namen, die aber hier nur verwirren würden. Deswegen habe ich alles vereinfacht.

    zu b) Oh je, was ist eine Skalarfunktion? Mit Funktionen habe ich noch nie gearbeitet. Hast du dafür ein Beispiel?

    23 februarie 2012 12:08
  • Hallo KlausDieter,

    das sieht nicht viel anders aus als eine SP. Hier mal ein rudimentäres Beispiel.

    CREATE FUNCTION fct_GetIrgenwas
    (
        @Param1 INT,
        @Param2 NVARCHAR( 50 )
    )
    RETURNS INT
    AS
    BEGIN
    
    DECLARE @Result INT
    
        SELECT @Result = <Irgendwas>
        FROM   <Tabelle>
        WHERE  <Spalte1> = @Param1
        AND    <Spalte2> = @Param2
    
        RETURN @Result
    
    END
    GO
    


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

    23 februarie 2012 12:14
  • Aus Performance-Gründen rate ich dringend von einer Skalarfunktion ab.

    Stattdessen bitte eine Inline Table-Valued Function verwenden:

    CREATE FUNCTION itvf_GetIrgenwas
    (
        @Param1 INT,
        @Param2 NVARCHAR( 50 )
    )
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT <Irgendwas>
        FROM   <Tabelle>
        WHERE  <Spalte1> = @Param1
        AND    <Spalte2> = @Param2
    )
    GO;

    Der Aufruf sieht dann ungefähr so aus:

    SELECT t1.Spalte1, t1.Spalte2, irgendwas FROM <Tabelle> t1 CROSS APPLY itvf_GetIrgendwas(<Wert1>, t1.SpalteX) as Sub;

    Der Grund für den Performance-Unterschied:

    wenn eine Skalarfunktion im SELECT-Teil einer Abfrage verwendet wird, so wird diese Funktion 1x PRO ZEILE ausgeführt! Wenn die Abfrage also 1.000 Zeilen zurückgibt, dann wird der in der Funktion definierte SELECT auch 1.000x ausgeführt. Gleiches gilt für Skalarfunktionen (oder Mulit-Statement-Funktionen) in der WHERE-Bedingung (dort ist der Effekt üblicherweise sogar noch extremer).

    Wozu also überhaupt Skalar-Funktionen? Aus meiner Sicht nur für die Kapselung von Berechnungen mit diskreten Werten bzw. Werten aus einer Zeile. Sobald ich ein "FROM" oder irgendein "Schleifenzeichen" in einer Skalar-Funktion sehe, ist dies für mich ein Alarmsignal. ;-)

    23 februarie 2012 13:16
  • Aus Performance-Gründen rate ich dringend von einer Skalarfunktion ab.

    ...

    Danke für die Erläuterung. Das von mir gewählte Beispiel ist sicherlich suboptimal. Allerdings habe ich KlausDieter so verstanden, dass er wohl bestimmte Daten pro einzelner Zeile "errechnen" will. Da würde es IMHO keinen merkbaren Unterschied zwischen deiner und meiner Variante geben, da hierbei dann dennoch die Berechnungen pro Zeile ausgeführt werden müssen, oder?


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

    23 februarie 2012 13:31
  • Aus Performance-Gründen rate ich dringend von einer Skalarfunktion ab.

    ...

    Danke für die Erläuterung. Das von mir gewählte Beispiel ist sicherlich suboptimal. Allerdings habe ich KlausDieter so verstanden, dass er wohl bestimmte Daten pro einzelner Zeile "errechnen" will. Da würde es IMHO keinen merkbaren Unterschied zwischen deiner und meiner Variante geben, da hierbei dann dennoch die Berechnungen pro Zeile ausgeführt werden müssen, oder?


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

    In dem von Dir genannten Beispiel handelt es sich um die o.g. "Kapselung von Berechnungen mit diskreten Werten bzw. Werten aus einer Zeile". Somit würde ich wahrscheinlich auch die Skalarfunktion nutzen. Die entscheidende Frage ist: Kann das Ergebnis ausschließlich auf Basis der Werte einer Zeile (+ evtl. weiterer bekannter Variablen) ohne Schleifenfunktion ermittelt werden? Dann käme eine Skalarfunktion in Betracht. Sollte sich jedoch herausstellen, daß diese Funktion auf Grund der Rechenoperation als Solches erheblich lange dauert, kommt u.U. eine CLR-Funktion in Betracht.

    Ein klassicher Fall von "Es kommt darauf an!" :-))

    MfG

    Lutz


    • Editat de LMU92 23 februarie 2012 14:38 Syntaxfehler :-)
    23 februarie 2012 13:40
  • Hi Stefan,

    vielen Dank für den Tipp - jetzt habe ich Neuland kennengelernt ;-) . Nachdem ich mich an die Syntax gewöhnt habe, klappte es vorzüglich.

    Später kam noch der Einwand von Lutz dazu: Diesen werde ich mal im Hinterkopf behalten, aber die Skalarfunktion ist für meinen Zweck (gelegentliche Auswertungen)  sehr schnell (ca. 5000 Datensätze in 3s), sodas ich auch dabei bleiben kann.

    Gruß, Klaus-Dieter

    23 februarie 2012 14:26
  • Hi Stefan,

    vielen Dank für den Tipp - jetzt habe ich Neuland kennengelernt ;-) . Nachdem ich mich an die Syntax gewöhnt habe, klappte es vorzüglich.

    Später kam noch der Einwand von Lutz dazu: Diesen werde ich mal im Hinterkopf behalten, aber die Skalarfunktion ist für meinen Zweck (gelegentliche Auswertungen)  sehr schnell (ca. 5000 Datensätze in 3s), sodas ich auch dabei bleiben kann.

    Gruß, Klaus-Dieter

    Hallo Klaus-Dieter,

    hoffentlich sind die 5000 Datensätze eine eher statische Größe und wachsen nicht ständig an oder die "gelgentliche Auswertung" wird nicht zum "Multi-user-Dauerbrenner"...

    Ich bevorzuge die Methode, eine Lösung von Anfang an skalierbar zu gestalten.

    MfG, Lutz

    23 februarie 2012 14:45
  • Hallo Zusammen!

    Jetzt habe ich mich zu früh gefreut: Ich habe es an einem einfacheren Beispiel getestet und es hat prima geklappt. Jetzt wollte ich es auf ein schwierigeres (mein eigentliches Problem) übertragen und habe dazu ebenfalls eine SP adaptiert. Da kam aber die Fehlermeldung 'Cannot access temporary tables from within a function.' Dummerweise arbeite ich häufig mit Temp-tabellen. Kann man das eleganter lösen? Oder kann man wenigstens mit einer festen Tabelle arbeiten, die am Ende jeweils geleert wird?

    Speziell @ Lutz: Du hat prinzipiell Recht, aber diese Abfrge wird nur von mir selbst gemacht und dient zu gelegentlichen Auswertungen. Die Tabellengröße wächst auch nicht signifikant.

    VG Klaus-Dieter

    23 februarie 2012 15:18
  • Die TempTables sollten sich durch CTE's (allgemeine Tabellenausdrücke) ersetzen lassen, die das Statement zur Erstellung der Temp Tables beinhalten. Damit wird jedoch von der Funktion auf die Ursprungstabellen zugegriffen.

    23 februarie 2012 15:33
  • Vielen Dank für den Tipp mit CTEs - ich werde mich da mal schlau machen.

    VG Klaus-Dieter

    24 februarie 2012 08:58
  • Hallo KlausDieter L,

    Ich gehe davon aus, dass die Antworten Dir weitergeholfen haben.
    Solltest Du noch "Rückfragen" dazu haben, so gib uns bitte Bescheid.

    Grüße,
    Robert


    Robert Breitenhofer, MICROSOFT  Twitter Facebook
    Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip „Entwickler helfen Entwickler“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.

    26 martie 2012 08:56