Run Stored Procedure in SQL Server from Excel

excel-run-stored-procedure.vba

Private Function getInfoFromStoredProcedure() As ADODB.Recordset
 
    On Error GoTo Error:
 
    Dim adoCon As ADODB.Connection
    Dim adoCmd As ADODB.Command
    Dim rst As New ADODB.Recordset
   
    Set adoCon = New ADODB.Connection
    Set adoCmd = New ADODB.Command
   
    'Create and open connection
    adoCon.ConnectionString = strDbConn
    adoCon.Open
   
    'Create command & link to stored proc & connection
    adoCmd.CommandType = adCmdStoredProc
    adoCmd.CommandText = "sp_StoredProcedureName" ' Name of stored procedure
    adoCmd.ActiveConnection = adoCon
   
    'Add parameters to stored proc
    'adoCmd.Parameters.Append adoCmd.CreateParameter("@myParam", adVarChar, adParamInput, Len(myParam), myParam)
   
    'Open command to execute query and return recordset
    Set getInfoFromStoredProcedure = New ADODB.Recordset
    getInfoFromStoredProcedure.Open adoCmd
      
    Set adoCmd = Nothing
    Set adoCon = Nothing
    Exit Function
 
Error:
    Debug.Print "error"
    Exit Function
   
End Function