Useful Stored Procedure Multiple Parameter Passing Function (VB6)

[kevchadders]

(For VB6)

Here is an old piece of code i have used for a while. I find this very useful when calling SQL Stored Procedures, which have any number of parameters being to be passed to them.

' *********************************************************************************************
' Function ExecStoredProc
'
' Description:      Executes a SQL stored procedure within the specified database,
'                   using parameters passed by the user. Uses a parameter array
'                   to accept and store any possible number of supplied parameters
' Inputs:           Stored procedure name, connection string, name of returned recordset,
'                   stored procedure parameters (any number allowed)
' Outputs:          Returns an integer value representing the SQL stored procedure
'                   return code. 0 = success, -1 indicates VB error,
' Author:           Kevin Chadwick
'
' ---------------------------------------------------------------------------------------------
' Ammendments
' *********************************************************************************************

Public Function ExecStoredProc(ByVal vstrSpName As String, ByRef robjRecordSet As ADODB.Recordset, _
    ParamArray vntArray()) As Integer
    
    ' On error
    On Error GoTo ExecStoredProcError
    
    ' Declare the variables
    Dim objCommand As ADODB.Command
    Dim intCurrentParameter As Integer
    
    ' Create the objects
    Set objCommand = New ADODB.Command
                    
    ' Make it an active connection
    objCommand.ActiveConnection = gconYourConnection
               
    'Declare the command object as a stored procedure
    objCommand.CommandType = adCmdStoredProc
    objCommand.CommandText = vstrSpName 'supplied by calling program

    'Get the supplied parameters from the ParamArray and assign them to the command object
    objCommand.Parameters.Append objCommand.CreateParameter("RetValue", _
        adInteger, adParamReturnValue) 'delcares the SQL return value

    For intCurrentParameter = 1 To (UBound(vntArray) + 1)
        If vntArray(intCurrentParameter - 1) <> "" Then
            objCommand.Parameters.Append objCommand.CreateParameter("Parameter" _
            & intCurrentParameter, adVarChar, adParamInput, Len(vntArray(intCurrentParameter - 1)), vntArray(intCurrentParameter - 1))
        Else
            objCommand.Parameters.Append objCommand.CreateParameter("Parameter" _
            & intCurrentParameter, adVarChar, adParamInput, 1)
        End If
    Next intCurrentParameter

    'Get the recordset back
    Set robjRecordSet = New ADODB.Recordset
    robjRecordSet.CursorLocation = adUseClient
    Set robjRecordSet = objCommand.Execute

    'Clean up, send the SQL return code back to the calling program, and exit
    ExecStoredProc = objCommand.Parameters(0).Value 'send return code back
    Set objCommand = Nothing
       
   
    '--Error Handling--
ExecStoredProcError:
    ' Add your error handling here

End Function

You would use this as follows

ExecStoredProc("sp_storedprocname", lrsYourRecordSet, param1, param2, etc...)

Note: With the list of parameters, you must make sure they are passed in the same order as whats in your SQL Stored Procedure