Stored Procedure über Schleife ausgeben

Question

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?

Answer 1

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

Answer 2

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'.

Answer 3

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

Answer 4

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?

Answer 5

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

Answer 6

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. ;-)

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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.

Answer 13

Vielen Dank für den Tipp mit CTEs - ich werde mich da mal schlau machen.

VG Klaus-Dieter

Answer 14

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 
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.