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