Zur Hauptseite ... Zum Onlinearchiv ... Zum Abonnement ... Zum Newsletter ... Zu den Tools ... Zum Impressum ... Zum Login ...

Achtung: Dies ist nicht der vollständige Artikel, sondern nur ein paar Seiten davon. Wenn Sie hier nicht erfahren, was Sie wissen möchten, finden Sie am Ende Informationen darüber, wie Sie den ganzen Artikel lesen können.

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

Gedrucktes Heft

Diesen Beitrag finden Sie in Ausgabe 5/2008.

Unser Angebot für Sie!

Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'!

Diesen Beitrag twittern

Zusammenfassung

Tipps und Tricks zum Einsatz von gespeicherten Prozeduren in einer MDB

Techniken

SQL Server 2005, Access 2000-2007, VBA, T-SQL

Voraussetzungen

Access, VBA, T-SQL

Beispieldateien

SPs.mdb, spUpdateInsertRegion.sql,
spBestellungenKundeMitAnzPos.sql

Shortlink

626

Gespeicherte Prozeduren

Bernd Jungbluth, Horn

"Oh - by the way: The same procedure as last year, Miss Sophie?" "The same procedure as every year, James." Auch der SQL Server bietet Procedures: Stored Procedures. Und wie auch Miss Sophie greift der SQL Server gerne auf altbewährte Procedures zurück - natürlich weitaus öfter als nur einmal im Jahr und auch nicht immer nur auf ein und dieselbe Weise. Im Gegenteil: Sie können für die Verwaltung und Aufbereitung Ihrer Daten mehrere Stored Procedures in Ihrer Datenbank anlegen und diese immer wieder verwenden. Das sollten Sie auch ausgiebig tun, denn gerade Stored Procedures bieten das meiste Potenzial, wenn es um Geschwindigkeit, Datenkonsistenz und die Implementation von Geschäftslogik geht.

Eine Stored Procedure ist nichts anderes als eine Prozedur, in der, ähnlich einer VBA-Funktion, mehrere Anweisungen - hier natürlich SQL-Anweisungen - gespeichert werden. Daher auch der Name Stored Procedure (zu deutsch: gespeicherte Prozedur).

Warum aber sollten SQL-Anweisungen auf dem SQL Server gespeichert werden? Immerhin können die SQL-Anweisungen doch im Frontend wunderbar nach den dort gegebenen Umständen zur Laufzeit zusammengestellt und ausgeführt werden. Die Antwort auf diese Frage lautet: Geschwindigkeit.

Geschwindigkeit

Bevor der SQL Server eine SQL-Anweisung ausführt, prüft er zunächst die Syntax der SQL-Anweisung. Zu dieser Syntaxprüfung gehört nicht nur die Kontrolle der Syntax der einzelnen Befehle, sondern es wird auch die Existenz der dort angegebenen SQL Server-Objekte, wie Tabellen, Sichten und deren Spalten, geprüft. Diese Syntaxprüfung erfolgt vor jeder Ausführung der SQL-Anweisung.

Nach der Syntaxkontrolle wird die SQL-Anweisung zunächst kompiliert, das heißt, es wird ein Ausführungsplan für die Ermittlung der Daten durch den Abfrageoptimierer erstellt. Dieser Ausführungsplan ist mit einer Wegbeschreibung vergleichbar. Er enthält die Reihenfolge, in der etwa die Tabellen der SQL-Anweisung gelesen oder welche Indizes benutzt werden. Anhand dieses Ausführungsplans wird die SQL-Anweisung ausgeführt.

Auch bei der Ausführung einer gespeicherten Prozedur wird zunächst eine Syntaxkontrolle durchgeführt und ein Ausführungsplan erstellt. Doch im Gegensatz zur SQL-Anweisung nicht bei jeder, sondern nur bei der ersten Ausführung. Denn der Ausführungsplan wird im sogenannten Prozedurcache - also im Arbeitsspeicher - abgelegt. Bei der nächsten Ausführung entfallen Syntaxkontrolle und Kompilieren der gespeicherten Prozedur. Der SQL Server greift direkt auf den Ausführungsplan im Arbeitsspeicher zu. Und das macht die Ausführung von gespeicherten Prozeduren schnell - sehr schnell.

Aber nicht nur die Geschwindigkeit ist ein Trumpf der gespeicherten Prozeduren. Mit gespeicherten Prozeduren können Sie auch die Datenkonsistenz und das Einhalten von Geschäftsregeln gewährleisten.

Geschäftslogik und Datenkonsistenz

Durch die Möglichkeit, mehrere SQL-Anweisungen - ob SELECT, UPDATE, INSERT oder DELETE - in einer gespeicherten Prozedur zu hinterlegen, sind diese prädestiniert für die Umsetzung der Geschäftslogik auf dem SQL Server.

Der Vorteil liegt auf der Hand - oder besser gesagt auf dem SQL Server: Egal mit welchem Frontend die Daten der Datenbank verwaltet oder ausgewertet werden, sofern die Verarbeitung über gespeicherte Prozeduren stattfindet, werden immer dieselben Algorithmen beziehungsweise Regeln für die Auswertungen oder Datenmanipulationen verwendet. An diesen Regeln gibt es kein Vorbei.

Natürlich gibt es gewitzte Benutzer, die durchaus in der Lage sind, die Daten einer Tabelle über eine ODBC- oder OLEDB-Verbindung zu lesen oder gar zu ändern. Doch auch dies kann mit gespeicherten Prozeduren verhindert werden. Hier kommt das Rechtekonzept des SQL Servers ins Spiel: Sie nehmen den Benutzern jegliche Rechte an den Tabellen. Die Benutzer dürfen die Daten einer Tabelle weder lesen, ändern oder löschen noch neue Datensätze hinzufügen. Diese Aktionen sind nur über entsprechende gespeicherte Prozeduren möglich, für deren Ausführung die Benutzer die notwendigen Rechte haben. Eine Datenmanipulation direkt in den Tabellen über eine x-beliebige ODBC- oder OLEDB-Schnittstelle ist mit diesem Konzept nicht mehr möglich. Vielmehr können die Daten nur über die in den gespeicherten Prozeduren hinterlegten Regeln und Algorithmen der Geschäftslogik verändert und ausgewertet werden. Können Sie sich eine bessere Methode zur Gewährleistung der Datenkonsistenz vorstellen?

Nun sind Geschäftsregeln nicht gerade ein Fels in der Brandung im "Daily Business". Geschäftsregeln werden ständig modernisiert, verbessert und neuen Gegebenheiten angepasst. Das bedeutet ständige Anpassungen von bestehender Programmlogik beziehungsweise von Programmcode. Sofern Sie die Regeln und Algorithmen zur Datenaufbereitung beziehungsweise Datenverarbeitung in gespeicherten Prozeduren gekapselt haben, müssen Sie nicht zig Frontends an die neuen Regeln anpassen, sondern lediglich die betroffenen gespeicherten Prozeduren. Das Frontend bleibt von solchen Änderungen weitgehend unberührt. Lediglich bei Änderungen in der Parameterübergabe einer gespeicherten Prozedur muss diese im Frontend angepasst werden.

Dasselbe gilt für eine nachträgliche Änderung des Datenbankdesigns - was bei einer Datenbankentwicklung natürlich nie vorkommt. Falls dieser seltene Fall doch mal eintreten sollte, müssen nur die gespeicherten Prozeduren angepasst werden, die die Daten der betroffenen Tabellen auswerten beziehungsweise verwalten.

Sie sehen, mit gespeicherten Prozeduren haben Sie nicht nur Geschwindigkeitsvorteile, sondern auch eine gesunde Datenkonsistenz. Um jetzt noch in den Genuss von schnellen und stringenten gespeicherten Prozeduren zu kommen, müssen Sie nur eines tun: Sie müssen die gespeicherten Prozeduren schreiben.

Gespeicherte Prozeduren

Gespeicherte Prozeduren werden in T-SQL geschrieben. Diese Sprache ist die "Programmier"-Sprache des SQL Servers. Programmier-Sprache ist eigentlich etwas zu hoch gegriffen, denn T-SQL ist doch sehr spartanisch, was seine Möglichkeiten betrifft. Für Datenauswertungen beziehungsweise -manipulationen jedoch ist T-SQL absolut ausreichend und - bei aller Diskussion über Möglichkeiten, .NET innerhalb von gespeicherten Prozeduren verwenden zu können - auch weiterhin die schnellste Variante, Daten auf dem SQL Server zu verarbeiten. Und es kommen mit jeder neuen SQL Server-Version neue T-SQL-Befehle dazu.

T-SQL bietet das Notwendigste, um Daten strukturiert zu verarbeiten.

  • Sie können Eingabe- und Ausgabeparameter mit und ohne Standardwerte definieren.
  • Sie können Variablen deklarieren und nutzen.
  • Sie können Systemwerte abfragen, wie die Anzahl der geänderten Datensätze, Fehler usw.
  • Sie können IF...ELSE-Anweisungen durchführen.
  • Sie können Schleifen programmieren - was Sie bitte nur dann tun, wenn es nicht anders geht.
  • Sie können Zwischenergebnisse in temporären Tabellen beziehungsweise in Table-Variablen speichern.
  • Sie können gespeicherte Prozeduren innerhalb von gespeicherten Prozeduren aufrufen.
  • Sie können Sichten und benutzerdefinierte Funktionen verwenden.
  • Und last but not least ist es natürlich auch möglich, mit T-SQL Prozeduren zu schreiben, deren Ausführung eher langsam als schnell ist.

Der letzte Punkt ist durchaus ernst zu nehmen. Wie bei jeder Programmiersprache sollte der Programmierer auch bei T-SQL wissen, was er tut. Auch hier ist es sehr einfach, einen schlechten und aufgeblähten Code zu schreiben, der lange Verarbeitungszeiten und hohen CPU-Verbrauch produziert.

Doch genug der Theorie. Anhand eines Beispiels werden Sie nun sehen, wie eine gespeicherte Prozedur angelegt wird. In der Northwind-Datenbank soll eine gespeicherte Prozedur namens spBestellungenKundeMitAnzPos für den Besitzer dbo angelegt werden.

Dazu müssen Sie zunächst das SQL Server Management Studio öffnen und dort zur Northwind-Datenbank wechseln. Innerhalb der Datenbank erweitern Sie den Zweig Programmierbarkeit und öffnen dort mit der rechten Maustaste auf gespeicherte Prozeduren das Kontextmenü, in dem Sie den Befehl Neue gespeicherte Prozedur wählen (s. Abb. 1). Als Ergebnis erhalten Sie eine "Prozedurerstellungsabfrage" mit der Vorlage für eine neue gespeicherte Prozedur.

pic001.TIF

Abb. 1: Eine neue gespeicherte Prozedur

Gespeicherte Prozeduren werden mit CREATE PROCEDURE angelegt. Innerhalb dieser Anweisung müssen der Name der Prozedur, die Parameter und selbstverständlich auch die Anweisungen der Prozedur enthalten sein.

Der Name der gespeicherten Prozedur darf keine Leerzeichen oder eckigen Klammern enthalten, wohingegen Unterstriche erlaubt sind. Jedoch steckt in der Verwendung von Unterstrichen eine kleine Performancefalle. Ihre gespeicherten Prozeduren sollten nicht mit sp_ beginnen. Dieses Präfix ist für die systemeigenen Prozeduren des SQL Servers vorgesehen. Es hindert Sie allerdings nichts und niemand daran, dieses Präfix zu nutzen. Sie erhalten auch keine Fehlermeldung. Aber der SQL Server interpretiert gespeicherte Prozeduren, die mit sp_ beginnen, als Systemprozeduren und sucht diese zunächst in der master-Datenbank - und erst danach in der aktuellen Datenbank.

Zum Namen gehört auch der Bezeichner des Schemas. Über das Schema eines Objekts werden unter anderem die Zugriffsrechte verwaltet. Es würde jedoch den Rahmen dieses Artikels sprengen, detailliert auf die Schemata einzugehen. Um es an dieser Stelle so kurz wie möglich zu halten: Hier ist von dem Zusatz dbo die Rede. Geben Sie vorab immer das Schema dbo bei der Erstellung von SQL Server-Objekten an, um eine einheitliche Basis für die Weiterentwicklung und für eine spätere Konfiguration eines Berechtigungskonzepts zu haben.

Nach der Namensgebung folgt die Definition der Parameter. Ein Parameter wird hinter dem Namen der Prozedur in Klammern definiert. Mehrere Parameter werden dabei durch Komma getrennt. Eine Prozedur muss keine, kann aber bis zu 1.024 Parameter enthalten. Jeder einzelne Parameter ist mit Namen, Datentyp und Richtung und ggf. mit einem Standardwert zu definieren.

Die Richtung zeigt den Weg des Parameters. Es gibt Input- wie auch Output-Parameter. OUTPUT-Parameter werden mit der Erweiterung OUTPUT gekennzeichnet und geben einen Wert aus der gespeicherten Prozedur an die aufrufende Instanz zurück.

CREATE PROCEDURE spInputOutput

(

@InputParameter int,

@OutputParameter varchar(100) OUTPUT

)

AS

BEGIN

...

Die Datentypen der Parameter entsprechen den Datentypen, die auch bei Tabellen verwendet werden. Jeder Parameter muss mit einem @-Zeichen beginnen. Dies gilt auch für jede lokale Variable, die innerhalb der gespeicherten Prozedur genutzt wird.

Mit den lokalen Variablen sind auch schon die ersten Komponenten des eigentlichen Programmcodes einer gespeicherten Prozedur angesprochen. Der Programmcode wird über die Anweisung AS nach der Parameterdefinition eingeleitet.

Lokale Variablen werden in derselben Art wie die Parameter deklariert - mit der Ausnahme, dass bei der Deklaration nicht direkt ein Wert zugewiesen werden kann. Dies ist erst seit SQL Server 2008 möglich. Eine Initialisierung können Sie entweder in einzelnen SET-Anweisungen oder aber - bei der Initialisierung von mehreren Variablen - in einer einzigen SELECT-Anweisung durchführen. Letzteres ist die effektivere Methode, da hier für die Initialisierung der einzelnen Variablen nur eine einzige Anweisung ausgeführt wird. Bei SET werden die Variablen einzeln initialisiert, was auch einzelne Anweisungen zur Folge hat.

CREATE PROCEDURE spInputOutput

(

@InputParameter int,

@OutputParameter varchar(100) OUTPUT

)

AS

BEGIN

DECLARE @Var1 int

DECLARE @Var2 datetime

SET @Var1 = 0

SET @Var2 = getdate()

...

oder

CREATE PROCEDURE spInputOutput

(

@InputParameter int,

@OutputParameter varchar(100) OUTPUT

)

AS

BEGIN

DECLARE @Var1 int, @Var2 datetime

SELECT @Var1 = 0, @Var2 = getdate()

...

Innerhalb des Programmcodes stehen alle Möglichkeiten von SQL zur Verfügung. Dazu kommen noch Programmsteuerungsbefehle wie BEGIN...END, IF...ELSE, WHILE, BREAK, CONTINUE, EXECUTE, GOTO, PRINT, RETURN, WAITFOR u.v.m.

Jeder zusammengehörige Block von Anweisungen wird mit einem BEGIN und END eingekreist. Besonders bei IF...ELSE-Anweisungen sind diese BEGIN...END-Blöcke immens wichtig, um die Verarbeitung mehrerer Anweisungen etwa im IF-Part zu kennzeichnen.

...

IF @Var1 > 0 THEN

    BEGIN

    SELECT @Var1

    RETURN 0

    END

Else

    BEGIN

    SELECT ‘Fehler‘

    RETURN 1

    END

    ...

Das Ergebnis der Prozedur wird abhängig von ihrer Art und Aufgabe zurückgegeben. Gespeicherte Prozeduren können zur Datenmanipulation, aber auch zur Datenaufbereitung verwendet werden. Bei der Datenaufbereitung liefern die jeweiligen SELECT-Anweisungen innerhalb der gespeicherten Prozedur die Ergebnismengen an die aufrufende Instanz.

In beiden Fällen - ob Datenaufbereitung oder Datenmanipulation - wird von der gespeicherten Prozedur ein Return-Wert vom Datentyp integer an die aufrufende Instanz zurückgegeben. Dieser Wert kann als Kennzeichnung, ob die gespeicherte Prozedur erfolgreich ausgeführt wurde oder ein Fehler eingetreten ist, weiterverwendet werden. Die Systemprozeduren des SQL Servers liefern eine 0 bei einer fehlerfreien Ausführung und eine 1, wenn die Anweisung mit einem Fehler beendet wurde. Diese Vorgehensweise ist nicht zwingend vorgeschrieben, aber empfehlenswert. Den Return-Wert übergeben Sie mit der Anweisung RETURN.

Die Anweisung RETURN ist immer die letzte Anweisung, die in einer gespeicherten Prozedur ausgeführt wird. Sie können diese Anweisung mehrfach an beliebigen Stellen in einer gespeicherten Prozedur angeben. Sobald aber die Anweisung das erste Mal ausgeführt wird, ist die gespeicherte Prozedur beendet. Der nachfolgende Code wird nicht mehr ausgeführt.

Jede Anweisung beziehungsweise jeder zusammengehörende Anweisungsblock innerhalb einer gespeicherten Prozedur sollte mit einem Semikolon abgeschlossen werden. Das Semikolon ist für die SQL Server-Versionen bis einschließlich SQL Server 2005 nicht zwingend erforderlich. Ab SQL Server 2008 aber ist ein Semikolon ein syntaktisches Muss. Je früher Sie sich also an das Semikolon gewöhnen, desto weniger sind Sie ab der Version 2008 auf Fehlersuche.

So weit ein grober Überblick zur Erstellung einer gespeicherten Prozedur. In Listing 1 sehen Sie nun die gespeicherte Prozedur dbo.spBestellungenKundeMitAnzPos, die die Bestellungen eines Kunden abhängig von der Anzahl der Bestellpositionen auflistet.

Listing 1: Gespeicherte Prozedur dbo.spBestellungenKundeMitAnzPos

Sie haben das Ende des frei verfügbaren Teils des Artikels erreicht. Lesen Sie weiter, um zu erfahren, wie Sie den vollständigen Artikel lesen und auf viele hundert weitere Artikel zugreifen können.

Sind Sie Abonnent?Jetzt einloggen ...
 

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

Verwandte Beiträge:

Abfragen von Access zum SQL Server

Die Microsoft Data Engine (MSDE)

DBMS-unabhängiger Zugriff auf SQL Server und Co.

Upsizing von Access nach SQL Server: Tabellen

Der DBMS-Connection-Wizard

Anmeldung an SQL Server und Co.

Benutzerdefinierte Funktionen im MS SQL Server

Trigger

Datensicherung mit dem MS SQL Server

© 2003-2015 André Minhorst Alle Rechte vorbehalten.