Repeating executing stored procedure

[Jusenkyo]

Hello all

I have 4 stored procedures that each require a multitude on input parameters.
I am feeding these in through a form in VB as follows:

cmd.CommandText = "sp1"
cmd.CommandType = adCmdStoredProc

Set prmNTAddedBy = cmd.CreateParameter("NTAddedBy", adVarChar, adParamInput, 50, Me.Name)
cmd.Parameters.Append prmNTAddedBy
etc...

Set rst = cmd.Execute

This is obviously repeated for all of the fields, and all of the stored procedures.

My problem is that these parameters from the form are inputted into each stores procedure, so essentially, I am repeating about 40 lines of code 4 times!

My question is this. Is there a way to grab the parameters in VB, and just feed them into all 4 stored procedures one after another?

Cheers in advance!
J

 

[hkaing79]

Don't you just add the parameters in your function call?

Function f1(cmdText AS String)
cmd.CommandText=cmdText
...
End Function

Create another looping function
Function Main()
Dim i as Integer

For i=1 to 40
f1("sp" & i)
Next
End Function

 

[hwkranger]

I have a class that I use to do this... You can modify it for your own purposes.

Option Compare Database
Option Explicit

'CLASS CLSSTOREDPROC
' Class Executes a Stored Procedure.

'PRIVATE VARIABLES
' col_Args is the collection of Parameters of the Stored Procedure
' cls_Param is an instance of the class clsSp_Parameter - has Name, Value
' str_sp_Name is the name of the stored Procedure

'Public Properties

Private col_Args As New Collection
Private cls_Param As clsSp_Parameter
Private str_sp_Name As String

Public Property Let Name(str_Name As String)
    str_sp_Name = str_Name
End Property

Public Sub ExecuteSP()
    If Validate Then
        Dim str_sp As String
        Dim str_spot As String
        str_spot = " "
        Dim i As Integer
        str_sp = "exec " & str_sp_Name
        For i = 1 To col_Args.Count
            str_sp = str_sp & str_spot & "@" & col_Args.Item(i).Name & "=" & col_Args.Item(i).value
            str_spot = ", "
        Next i
        DBCnn.Execute str_sp
    End If
End Sub

Public Sub DestroyParameters()
    Dim i As Integer
    For i = col_Args.Count To 1 Step -1  'col_Args.Count
        col_Args.Remove (i)
    Next i
    'Set col_Args = Nothing
End Sub

Public Sub DestroyCollection()
    DestroyParameters
    Set col_Args = Nothing
End Sub

Public Sub AddParameter(ByRef sp_param As clsSp_Parameter)
    Call col_Args.Add(sp_param, sp_param.Name)
    Set sp_param = Nothing
End Sub

Public Function GetParameter(str_Name As String) As Variant
    GetParameter = col_Args.Item(str_Name).value
End Function

Private Function Validate() As Boolean
    If Len(str_sp_Name) > 0 And col_Args.Count > 0 Then
        Validate = True
    End If
End Function

Private Sub Class_Initialize()
    Call fCheckConnect
End Sub

and

'CLASS clsSp_Parameter
Option Compare Database

Private str_Param_Name As String
Private str_Param_Value As String
Private bool_UseQuote As Boolean

Private Const Quote As String = "'"

Public Property Let Name(strParam_Name As String)
'Strips the @ so that whether or not the developer enters the @ it'll work
    str_Param_Name = Replace(strParam_Name, "@", "")
End Property

Public Property Get Name() As String
    Name = str_Param_Name
End Property

Public Property Let value(strParam_Value As String)
    str_Param_Value = strParam_Value
End Property

Public Property Get value() As String
    Dim q As String
    q = ""
    If UseQuote Then
        q = Quote
    End If
    value = q & Replace(str_Param_Value, "'", "") & q
End Property

Private Function UseQuote() As Boolean
If bool_UseQuote = True Or Me.Name = "Branch" Or Me.Name = "RPTDATE" Or Me.Name = "DESC" Or Me.Name = "BranchName" Or Me.Name = "TransDate2" Or Me.Name = "Firstname" Or Me.Name = "LastName" Or Me.Name = "BALFWD" Or Me.Name = "SSN2" Or Me.Name = "SOURCE" Or Me.Name = "GL_IDT" Or Me.Name = "PIC" Or Me.Name = "ACCT_DESC" Or Me.Name = "Teller" Then
    UseQuote = True
ElseIf Me.Name = "Branch" Or Me.Name = "br_Dept" Or Me.Name = "BranchNo" Or Me.Name = "Br" Then
    UseQuote = True
End If
End Function

Private Sub Class_Initialize()
    bool_UseQuote = False
End Sub

The use:

Public Function MoveImport(str_ImportName As String)
    Dim rst As DAO.RecordSet
    Set rst = CurrentDb.OpenRecordset("Local" & str_ImportName & "Import")
    If rst.RecordCount Then
        rst.MoveFirst
        Dim s As New clsStatusBar
        Dim i As Integer
        i = 0
        Dim sp As New clsStoredProc
        Dim p As New clsSp_Parameter
        
        sp.Name = "usp_atbl" & str_ImportName & "Import_Insert"
        s.Steps = Int(rst.RecordCount / 10)
        s.MeterText = "Importing " & str_ImportName
        Do While Not rst.EOF
            i = i + 1
           Dim fld As DAO.Field
            For Each fld In rst.Fields
                p.Name = fld.Name
                p.value = LTrim(RTrim(Nz(rst(fld.Name), "")))
                If Len(p.value) > 0 Then
                    Call sp.AddParameter(p)
                End If
            Next fld
            sp.ExecuteSP
            sp.DestroyParameters
                If i Mod 10 = 0 Then
                    s.MeterText = "Importing " & str_ImportName & " Record: " & i
                    s.MoveMeterBar
                End If
                rst.MoveNext
        Loop
        s.ClearBar
        sp.DestroyCollection
        Set s = Nothing
        Set p = Nothing
        Set sp = Nothing
    End If
End Function

Don't pay attention to the s, that's my status bar... What i did was named the parameters in the SP the same as the column names, this way I could roll through a table from access, and send it to the server via a SP. The data is trimmed and sanitized before hitting the server.

Randall Vollen
National City Bank Corp.

 

[Jusenkyo]

Excellent, thanks hwkranger! Ill take a look at this and let you know if I need more help!

 

[TomHW]

hwkranger,

I am curious as to why clsSp_Parameter.DestroyParameters removes each element of the collection before setting it equal to nothing. It is my understanding that setting a collection object equal to nothing will remove the entire collection from memory (being the last reference to the object). I was also wondering why clsSp_Parameter.AddParameter sets sp_param to nothing. I have never seen this done before and was wondering if it is a recommended method or a best practice.

Thanks,
Tom

 

[hwkranger]

Tom,

It seemed redundant to me, but I didn't know exactly how a collection was handled. I figured it was better safe than sorry, since this class is called 130,000+ times in a matter of 5 minutes. I use this to clean/transfer data that is imported from a text file into access tables to a production SQL2k Server.

I didn't see any negative impact to speed while testing, so I kept the release of each item in the collection.

The same is true for sp_param. Since it is a class, I wasn't sure how VB handled the release of the variable - I ussually set my variables to nothing when they are no longer needed. I always try to clean up my variables at the end of my work. Whether that is good practice or not, I'm not sure, but keeping a clean work space can't be 'that' bad.



Randall Vollen
National City Bank Corp.