Gespeicherte Prozeduren im MS SQL Server

By , 2. April 2011

Wer seine Access-Anwendung auf ein MS SQL Server (Express) umstellt, also migriert, stellt sich irgendwann sicherlich die Frage wie man die Performance von Abfragen erhöhen kann. Nach dem man die Abfragen optimiert hat –
Siehe z.B. Script zur 8. AEK: http://www.donkarl.com/AEK/AEK_Downloads.htm
– und sich immer noch nicht die gewünschte kurze Laufzeit von Abfragen einstellt, dann helfen zumeist sogenannte gespeicherte Prozeduren die man im SQL Server Management Studio von Microsoft anlegen kann.

Ich möchte diese nur kurz vorstellen anhand von 2 Beispielen, eines wo ein oder mehrere Feldwerte geändert werden (Setter-Funktion), und eine wo eine wo ein oder mehrere Feldwerte ausgelesen werden (Getter-Funktion).

gespeicherte Prozedur zur Änderung eines Feldwertes

Bild 1, gespeicherte Prozedur zur Änderung eines Feldwertes

Zu übergebende Parameter sind „ident“ und „Status“. Die Reihenfolge dieser Parameter ist später beim Aufruf der Gespeicherten Prozedur (engl. Kurzform = SP) von Bedeutung.
In der Prozedur lassen sich auch lokale Variablen deklarieren, wie z.B. sql als Platzhalter für den SQL-String.

Bei der Getter-Prozedur sieht das ein klein wenig anderst aus:

gespeicherte Prozedur um einen oder mehrere Feldwerte zu holen

Bild 2: gespeicherte Prozedur um einen oder mehrere Feldwerte zu holen

Am auffälligsten ist die Änderung im SQL-String. Anstatt dem aus Access gewohnten „Select Feldname From Tabelle“ schreibt man im Transact-SQL folgendes:
„Select @Rückgabeparameter=Feldname From Tabelle“
Außerdem wird der Rückgabeparameter „status“ bei der Deklaration mit dem Attribut „Output“ versehen.

In der Zeile wo die Prozedur ausgeführt wird (EXEC sp_executesql …) sieht man wie Parameter der Funktion sp_executesql() zu deklarieren sind. Dies kann man z.B. hier genauer nachlesen:
http://msdn.microsoft.com/de-de/library/ms188001.aspx

Eine weitere Möglichkeit ist den SQL-String direkt in die Prozedur zu schreiben, ohne einen Umweg über die sql-Variable zu machen, welche dann noch zusätzlich mittels einer Funktion ausgeführt werden muss ist die folgende:

GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getterFunction]
    @Status int OUTPUT,
    @Erfassungsdatum datetime Output,
    @ID int
AS 
    SET NOCOUNT ON 

    SELECT TOP 1 
        @Status               = A.Status,
        @Erfassungsdatum = B.ErfDatum
    FROM	
        Tabelle1 AS A INNER JOIN 	
        Tabelle2 As B
        ON A.ID = B.ID
    WHERE  B.ID = @ID 

    SET NOCOUNT OFF 

GO

Der Vorteil der ersten Variante mit der sp_executesql() Funktion ist, dass die Funktion bei Erfolg einen definierten Rückgabewert liefert (0), der direkt in der Prozedur abgefangen werden kann.
An der letzten Variante ist schön zu sehen wie man mehrere Rückgabewerte erhalten kann. Nach diesem Schema lässt sich die Prozedur beliebig erweitern auf mehrere Rückgabewerte.
Im nächsten Teil zeige ich wie man von Access aus diese gespeicheren Prozeduren aufruft, Parameter übergibt und Rückgabewerte ausliest.

Bis dahin
Andreas Vogt ©2011

Leave a Reply

You must be logged in to post a comment.

OfficeFolders theme by Themocracy