Gespeicherte Prozeduren in Access aufrufen

By , 8. April 2011

Im ersten Teil der Serie „Gespeicherte Prozeduren“ ging es darum wie im SQL Management Studio von Microsoft gespeicherte Prozeduren angelegt werden können. Es wurden zwei verschiedene Möglichkeiten einen SQL-String auszuführen erörtert, sowie zwischen „Getter“ und „Setter“ Prozeduren unterschieden. In diesem zweiten Teil geht es nun darum wie man diese Gespeicherten Prozeduren (SP für Stored Procedures) in Access nutzen kann um ausgewählte Daten zu erhalten bzw. um Daten in der SQL Server Datenbank zu speichern.

Ich erstelle zu diesem Zweck immer ein separates Modul mdlStoredProcedures worin ich verschiedene Prozeduren erstelle, je nach Anwendungszweck.
Ich unterscheide dabei zwischen Universellen Prozeduren und Speziellen Prozeduren.

Universelle Prozeduren bedienen immer mehrere gleichartige Gespeicherte Prozeduren im SQL Server. Beispiel dazu sind z.B. Prozeduren wo ein Status geändert wird. Nachfolgend als Generelles Beispiel für eine Setter-Prozedur in Access:

Deklarationsteil:

Dim cmdObj As ADODB.Command
Dim cnn As ADODB.Connection
Dim ConnectionString As String

Prozedur:

Public Function setStatus1(idWert As String, Prozedurname As String, Status As Long) As Boolean
    ConnectionString = "Driver={SQL Server};Server=?;Database=?;Uid=?????;Pwd=?;"
    Set cnn = OpenConnection(ConnectionString)
    Set cmdObj = New ADODB.Command
    With cmdObj
        .ActiveConnection = cnn
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@indent", adVarChar, adParamInput, 10, idWert)
        .Parameters.Append .CreateParameter("@status", adVarChar, adParamInput, 2, CStr(Status))
        .CommandText = Prozedurname
        .Execute
    End With
    Set cmdObj = Nothing
    Set cnn = Nothing
End Function

Der Prozedurname wird als Parameter übergeben und der Eigenschaft CommandText des ADODB Objektes zugewiesen.
An dieser Stelle sei erwähnt, dass wir mit ADO arbeiten, und daher den Verweis „Microsoft ActiveX Data Objects 2.1 Library“ benötigen.
Die Übergabeparameter müssen in der richtigen Reihenfolge stehen wie in der SP definiert. Die Parameter der Funktion CreateParameter() sind folgende:
Parametername, Datentyp, Input/Output-Typ, Zeichenlänge, Wert

Eine andere Variante ist die folgende, wobei ich nur den With-Teil poste da der Rest unverändert ist:

    With cmdObj
        .ActiveConnection = cnn
        .CommandText = Prozedurname
        .CommandType = adCmdStoredProc
        .CommandTimeout = 60
        .Parameters("@indent") = idWert
        .Parameters("@status") = CStr(Status)
        .Execute
    End With

Wie ihr seht, muss man sich bei dieser Variante nicht um Datentyp und Feldlänge kümmern. Zusätzlich habe ich die Eigenschaft CommandTimeOut gesetzt, um die maximale Laufzeit der Prozedur im SQL-Server zu begrenzen. Ich bevorzuge die letztere Version.

Um eine SP noch universeller zu halten kann man diese im SQL Server auch so gestalten dass sowohl Tabellennamen als auch Feldnamen als Parameter ausgeführt sind. So eine SP könnte z.b. so ausschauen (Auszug):

CREATE PROCEDURE [dbo].[getStatus]
 @tblname	nvarchar(127),
 @field		nvarchar(127),
 @ident		nvarchar(10),
 @status		int Output,
 @debug		bit = 0 AS
 Declare @sql nvarchar(1000)
 set @sql = 'Select @status=Status From ' + quotename(@tblname) + ' Where ' + quotename(@field) + ' = ' + @ident
 IF @debug = 1 PRINT @sql
 EXEC sp_executesql @sql, N'@status int output', @status Output

Ich habe dadurch 5 einzelne Gespeicherte Prozeduren ersetzt. Der Aufruf in Access sieht dann wie folgt aus:

Public Function GetStatus2(idWert As String, Komponente As String, Status As Long) As Boolean
    ConnectionString = "Driver={SQL Server};Server=?;Database=?;Uid=?;Pwd=?;"
    Set cnn = OpenConnection(ConnectionString)
    Set cmdObj = New ADODB.Command
    With cmdObj
        .ActiveConnection = cnn
        .CommandText = "getStatus"
        .CommandType = adCmdStoredProc

        Select Case LCase(Komponente)
            Case "kom1":
                tblname = "Tabelle1"
                field = "Feld1"
            Case "kom2":
                tblname = "Tabelle2"
                field = "Feld2"
            Case "kom3":
                tblname = "Tabelle3"
                field = "Feld3"
            Case "kom4":
                tblname = "Tabelle4"
                field = "Feld4"
            Case Else
                Exit Function
        End Select

        .Parameters.Append .CreateParameter("@tblname", adVarChar, adParamInput, 127, tblname)
        .Parameters.Append .CreateParameter("@field", adVarChar, adParamInput, 127, field)
        .Parameters.Append .CreateParameter("@indent", adVarChar, adParamInput, 10, idWert)
        .Parameters.Append .CreateParameter("@status", adVarChar, adParamInput, 2, CStr(Status))
        .Parameters("@status") = Null
        .Execute
        'Rückgabewert holen
        GetStatus2 = .Parameters("@status").Value
    End With
    Set cmdObj = Nothing
    Set cnn = Nothing
End Function

Soweit erst mal zu diesem Thema heute. Im dritten und vorerst letzten Teil der Reihe werden noch weitere Aspekte erläutert, wie z.B. mehrere Rückgabewerte, Ermitteln betroffener Datensätze etc.

Bis dahin
Andreas Vogt ©2011

Leave a Reply

You must be logged in to post a comment.

OfficeFolders theme by Themocracy