Useful Stored Procedure Multiple Parameter Passing Function (VB6)

(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.

Code:

---

' *********************************************************************************************
' 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