24.5 Gespeicherte Prozeduren (Stored Procedures)

Eine gespeicherte Prozedur (Stored Procedure, SP) ist eine Gruppe von SQL-Anweisungen, die kompiliert auf dem Datenbankserver als Teil einer Datenbank gespeichert sind. Durch die Kompilierung und den geringeren Netzwerkverkehr (die Prozedur läuft auf dem Server) ist sie oft schneller als explizite SQL-Anweisungen. Ihr Aufbau ist einfach:

CREATE PROCEDURE Produktsuche ( 
   @Preis money, 
   @Menge smallint 
) AS 
   SELECT * 
   FROM Products 
   WHERE UnitPrice < @Preis AND UnitsOnOrder = @Menge

Diese gespeicherte Prozedur beschreibt eine Auswahlabfrage, die alle Artikel der Tabelle Products liefert, die eine bestimmte Preisgrenze unterschreiten und eine bestimmte Anzahl von Bestelleinheiten haben. Es ist einerlei, ob Sie den Teil hinter der As-Klausel als SqlClient-Datenprovider-Befehl schicken oder die gespeicherte Prozedur aufrufen.

Gespeicherte Prozeduren bieten sich an, wenn Kommandos sehr häufig ausgeführt werden sollen. Sie sind nicht nur leistungsfähiger als normale SQL-Kommandos, sondern bieten auch darüber hinaus weitergehende Möglichkeiten: Gespeicherte Prozeduren können Berechnungen ausführen, Ein- und Ausgabeparameter entgegennehmen (ähnlich wie Wert- und Referenzparameter) oder ein Resultat an den Aufrufer liefern.

Es gibt ausführliche Literatur zu gespeicherten Prozeduren. Ich werde Ihnen später an einem komplexeren Beispiel zeigen, wie gespeicherte Prozeduren mit ADO.NET-Code behandelt werden. Hier soll die Syntax (fast) kommentarlos angegeben werden. Optionale Teile sind in eckige Klammern gesetzt, und Alternativen werden durch | getrennt. Kursiv gesetzte Bestandteile können Sie Ihren Bedürfnissen entsprechend anpassen. Nicht immer sind alle Datentypen einer Datenbank als Rückgabetyp einer Funktion erlaubt, und Änderungen an Tabellen sowie einige Funktionsaufrufe in der Funktion können verboten sein.

CREATE|ALTER PROCEDURE 
name [@Parameter Typ [=NULL | Konstante] [OUTPUT]] 
[WITH [RECOMPILE] [ENCRYPTION]] AS Anweisung(en) [RETURN GanzeZahl]

CREATE|ALTER FUNCTION 
Name (@Parameter Typ [=Standardwert], ...) 
RETURNS Typ AS BEGIN Anweisung(en) RETURN END | 
RETURNS TABLE AS RETURN (select)       | 
RETURNS @Tabelle TABLE(Spalte(n)) AS BEGIN Anweisung(en) RETURN END

24.5.1 Gespeicherte Prozeduren in Visual Studio 2008 erstellen

Ein herkömmlicher SQL-Befehl wird vom Client gegen die Datenbank abgesetzt. Gespeicherte Prozeduren sind, sofern die Datenbank diese unterstützt, Elemente der Datenbank selbst, so wie beispielsweise Tabellen oder Sichten. Wenn Sie wollen, können Sie sehr einfach aus Visual Studio heraus gespeicherte Prozeduren zu einer Datenbank hinzufügen. Öffnen Sie dazu den Server-Explorer in Visual Studio. In diesem finden Sie den Knoten Datenverbindungen. Im Kontextmenü dieses Knotens wählen Sie Verbindung hinzufügen. Der Dialog ist in Abbildung 24.1 zu sehen. Tragen Sie im oberen Kombinationslistenfeld den Namen des Servers ein, auf dem die SQL Server-Datenbank installiert ist, zu der Sie Verbindung aufnehmen wollen. Achten Sie darauf, dass im Feld Datenquelle der Microsoft SQL Server eingetragen ist. Wenn nicht, können Sie die Einstellung ändern bzw. an den gewünschten Datenbankserver anpassen.

Haben Sie die Installationsvorgabe Microsoft SQL Server übernommen, ist die Windows-Authentifizierung eingestellt und Sie müssen, soweit Sie mit entsprechenden administrativen Rechten ausgestattet sind, keine Änderungen an den Anmeldeinformationen vornehmen. Anschließend wählen Sie die gewünschte Datenbank aus und können die eingestellten Verbindungsdaten testen.

Abbildung 24.1 Dialog zum Hinzufügen einer Datenbankverbindung

Im Server-Explorer wird die neue Verbindung zur Datenbank eingetragen. Unter den datenbankspezifischen Knoten finden Sie nun auch Gespeicherte Prozeduren (siehe Abbildung 24.2). Klicken Sie dann im Kontextmenü des Knotens auf Neue gespeicherte Prozedur hinzufügen.

Abbildung 24.2 Gespeicherten Prozeduren der Datenbank Northwind

Im Codeeditor wird daraufhin ein weiteres Fenster mit einer gespeicherten Prozedur geöffnet (siehe Abbildung 24.3). Jede gespeicherte Prozedur wird mit CREATE PROCEDURE eingeleitet. Dem schließt sich der Bezeichner an. Einige Teile der Struktur sind mit /*...*/ auskommentiert. Dazu gehört auch der Block, in dem alle Übergabeparameter angegeben werden. Hinter AS folgen die SQL-Anweisungen. Eine gespeicherte Prozedur wird mit einem optionalen RETURN abgeschlossen, das den Rückgabewert der gespeicherten Prozedur angibt.

Abbildung 24.3 Codeeditor für gespeicherte Prozeduren

Der Name der Prozedur sollte möglichst selbsterklärend sein, z. B. Produktsuche. Parameter werden zwischen CREATE PROCDURE und AS definiert. Dabei wird zuerst der Parametername angegeben, der das Präfix »@« haben muss. Dahinter folgt der Datentyp. Mehrere Parameter einer gespeicherten Prozedur werden durch Kommata getrennt.

Standardmäßig sind alle Parameter Eingabeparameter, die von der gespeicherten Prozedur zur Ausführung benötigt werden, selbst aber kein Resultat zurückliefern. Gespeicherte Prozeduren kennen aber auch Ausgabeparameter, die mit Referenzparametern vergleichbar sind. Diese liefern dem Aufrufer ein Ergebnis und werden mit OUTPUT gekennzeichnet.

Nachdem Sie den SQL-Code im Codeeditor eingetragen haben, speichern Sie die Prozedur. Sie wird allerdings nicht im Projekt abgelegt, sondern in der Datenbank, wie Sie im Server-Explorer sehen. Beim Speichern wird die Syntax überprüft, und Fehler werden mit einer Meldung quittiert.

Damit hört die Unterstützung durch Visual Studio aber nicht auf. Sie können Ihre neue gespeicherte Prozedur auch in der Entwicklungsumgebung testen. Dazu sollte sich der Mauszeiger über dem Codefenster der gespeicherten Prozedur befinden. Öffnen Sie dann das Kontextmenü, und wählen Sie Ausführen. Es öffnet sich der Dialog aus Abbildung 24.4. In ihm weisen Sie in der Spalte Wert den Parametern die gewünschten Daten zu.

Abbildung 24.4 Parameterdialog für gespeicherte Prozeduren

Nach einem Klick auf OK sehen Sie im Fenster Ausgabe das Ergebnis des Aufrufs (siehe Abbildung 24.5).

Abbildung 24.5 Ergebnis des Aufrufs der gespeicherten Prozedur »Produktsuche«

24.5.2 Gespeicherte Prozeduren aufrufen

Die soeben entwickelte gespeicherte Prozedur Produktsuche soll nun aufgerufen werden. Prinzipiell ähnelt der Weg dem, den wir beim Aufruf einer parametrisierten Abfrage beschritten haben. Es gibt aber einen ganz wichtigen Unterschied: Wir müssen dem SqlCommand-Objekt über die Eigenschaft CommandType ausdrücklich mitteilen, dass es kein SQL-Kommando, sondern eine gespeicherte Prozedur ausführen soll.

cmd.CommandType = CommandType.StoredProcedure

Die Eigenschaft ist vom Typ der gleichnamigen Enumeration (siehe Tabelle 24.2), die angibt, wie das unter der Eigenschaft CommandText angegebene Kommando zu interpretieren ist.

Mit der Einstellung CommandType.TableDirect repräsentiert die Eigenschaft CommandText einen Tabellennamen, das Äquivalent zum SQL-Befehl SELECT * FROM <Tabelle>.

Bisher haben wir die Eigenschaft CommandType nicht benutzt, weil wir immer ein SQL-Kommando abgesetzt haben, das durch die Standardeinstellung Text beschrieben wird. Da wir nun eine gespeicherte Prozedur ausführen wollen, müssen wir CommandType den Wert CommandType.StoredProcedure zuweisen. Das SqlCommand-Objekt benutzt diese Information, um die Syntax für den Aufruf der gespeicherten Prozedur zu generieren.

'...\ADO\Datenbankabfragen\Prozedur.vb

Option Strict On 
Imports System.Data.Common, System.Data.SqlClient 
Namespace ADO 
  Module Prozedur 
    Sub Test() 
      Dim con As DbConnection = New SqlConnection() 
      con.ConnectionString = "Data Source=(local);" & _ 
          "Initial Catalog=Northwind;Integrated Security=sspi"

      ' SqlCommand vorbereiten 
      Dim cmd As DbCommand = New SqlCommand() 
      cmd.CommandType = CommandType.StoredProcedure 
      cmd.CommandText = "Produktsuche" 
      cmd.Connection = con

      ' Parameter-Auflistung füllen 
      cmd.Parameters.Add(New SqlParameter("@Preis", SqlDbType.Money)) 
      cmd.Parameters.Add(New SqlParameter("@Menge", SqlDbType.SmallInt)) 
      cmd.Parameters("@Preis").Value = 10 
      cmd.Parameters("@Menge").Value = 0

      ' SqlCommand ausführen 
      con.Open() 
      Dim dr As DbDataReader = cmd.ExecuteReader() 
      While (dr.Read()) 
        Console.WriteLine("{0,-35}{1}", dr("ProductName"), dr("UnitPrice")) 
      End While 
      dr.Close() : con.Close() 
      Console.ReadLine() 
    End Sub 
  End Module 
End Namespace

Die SQL-Variante, cmd.CommandText = "EXECUTE Produktsuche 10, 0" und cmd.ExecuteReader(), ruft dieselbe Prozedur auf. Die allgemeine Syntax lautet:

EXECUTE [@Rückgabe =] name [Wert | Variable OUTPUT] 
EXECUTE [@Rückgabe =] name [@Parameter = Wert | Variable OUTPUT]

24.5.3 Komplexe gespeicherte Prozeduren

Eine gespeicherte Prozedur ist nicht immer so einfach aufgebaut wie Produktsuche. Eine gespeicherte Prozedur kann sowohl über die Parameterliste als auch über RETURN Werte an den Aufrufer zurückliefern. Dazu ein Beispiel:

CREATE PROCEDURE Produkt ( 
  @id int, 
  @Was varchar(40) OUTPUT, 
  @Preis money OUTPUT 
) AS 
  SELECT @Was=ProductName, @Preis=UnitPrice 
  FROM Products 
  WHERE ProductID=@id 
RETURN @@ROWCOUNT

Die gespeicherte Prozedur definiert neben dem Eingabeparameter @id mit @zuname und @vorname auch zwei Ausgabeparameter, denen beim Aufruf zwar kein Wert übergeben wird, die aber ein Resultat zurückliefern. Der Rückgabewert @@ROWCOUNT ist eine Systemfunktion von SQL Server, die die Anzahl der Zeilen angibt, auf die sich die letzte Anweisung ausgewirkt hat.

'...\ADO\Datenbankabfragen\ProzedurMitReturn.vb

Option Strict On 
Imports System.Data.Common, System.Data.SqlClient 
Namespace ADO 
  Module ProzedurMitReturn 
    Sub Test() 
      Dim con As DbConnection = New SqlConnection() 
      con.ConnectionString = "Data Source=(local);" & _ 
          "Initial Catalog=Northwind;Integrated Security=sspi"

      ' DbCommand vorbereiten 
      Dim cmd As DbCommand = New SqlCommand() 
      cmd.CommandType = CommandType.StoredProcedure 
      cmd.CommandText = "Produkt" 
      cmd.Connection = con

      ' Parameters-Auflistung füllen 
      cmd.Parameters.Add(New SqlParameter("@RetValue", SqlDbType.Int)) 
      cmd.Parameters.Add(New SqlParameter("@id", SqlDbType.Int)) 
      cmd.Parameters.Add(New SqlParameter("@Was", SqlDbType.VarChar, 40)) 
      cmd.Parameters.Add(New SqlParameter("@Preis", SqlDbType.Money)) 
      cmd.Parameters("@id").Value = 1

      ' Richtung der Parameter spezifizieren 
      cmd.Parameters("@RetValue").Direction = ParameterDirection.ReturnValue 
      cmd.Parameters("@Was").Direction = ParameterDirection.Output 
      cmd.Parameters("@Preis").Direction = ParameterDirection.Output

      ' Kommando ausführen 
      con.Open() 
      cmd.ExecuteNonQuery()

      ' DbParameterCollection auswerten 
      If CType(cmd.Parameters("@RetValue").Value, Integer) = 1 Then 
        Console.WriteLine("Artikel: {0}", cmd.Parameters("@Was").Value) 
        Console.WriteLine("Preis:{0}", cmd.Parameters("@Preis").Value) 
      End If 
      Console.WriteLine("{0} Datensatz gefunden.", _ 
                        cmd.Parameters("@RetValue").Value) 
      con.Close() 
      Console.ReadLine() 
    End Sub 
  End Module 
End Namespace

Im ersten Schritt wird nach dem Öffnen der Verbindung das DbCommand-Objekt definiert. Anschließend wird für jeden Parameter der gespeicherten Prozedur der Parameters-Auflistung ein SqlParameter-Objekt hinzugefügt. Als Parameter wird auch der von RETURN gelieferte Rückgabewert verstanden, also braucht der Aufruf insgesamt vier Parameter-Objekte.

DbParameter können unterschiedliches Verhalten haben; standardmäßig beschreiben sie einen Eingabeparameter. Abweichungen davon werden in der Direction-Eigenschaft des Parameter-Objekts festgelegt, die vom Typ ParameterDirection ist. Dabei handelt es sich um eine Enumeration mit den in Tabelle 24.3 gezeigten vier Konstanten.

Jetzt muss die Parameterliste gefüllt werden, um das DbCommand-Objekt anschließend auszuführen. Dazu wird dem Parameter @id die Spalte ProductID zugewiesen, anhand derer der gesuchte Artikel identifiziert werden soll. Weil die gespeicherte Prozedur keine Datensatzliste zurückgibt, genügt es, die Methode ExecuteNonQuery auf dem DbCommand-Objekt aufzurufen. Das Ergebnis des Aufrufs kann danach ausgewertet werden, indem sowohl der Inhalt des Rückgabewertes als auch der Inhalt der Ausgabeparameter abgerufen werden.

Ihre Meinung

Wie hat Ihnen das Openbook gefallen? Wir freuen uns immer über Ihre Rückmeldung. Schreiben Sie uns gerne Ihr Feedback als E-Mail an kommunikation@rheinwerk-verlag.de.