при помощи ADODB
dim cmdProc as ADODB.Command
dim prmValue as ADODB.Parameter
.... <connect to SQL Server> ...
set cmdProc = New ADODB.Comman
set prmValue = New ADODB.Parameter

cmdProc.ActiveConnection = <some connection>
cmdProc.CommandText = <some stored procedure>
cmdProc.CommandType = adCmdStoredProc

set prmValue = New ADODB.Parameter
prmValue.Type =
prmValue.Size =
prmValue.Value =

prmProc.Parameters.Append prmValue

и далее получить результат в recordset.