Returning a stored procedure, ado, access, vba

Question

I have the following stored procedure.

create procedure dbo.sp_getDailyStats 
    @Rundate varchar(10), @PrevRunDate  varchar(10) OUTPUT
as 
Declare @Claims bigint;
declare @Days bigint;
declare @Claims_Completed_prev bigint;
declare @Claim_inventory bigint;
declare @Inv30 bigint;
declare @Inv30NP bigint
declare @InvNP bigint

set @PrevRundate=convert(varchar(10),dbo.getPreviousBusinessDate(@Rundate),101)

----- not the whole code.  

I run it from the command line like this:

declare @myvare varchar(10)
declare @InputDate varchar(10)
set @InputDate='06/10/2015'
exec dbo.sp_getDailystats @InputDate, @myvare output

select @myvare; and it returns the previous date to @myware but I have to put output on that line to make it work.

Below is my ado code:

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

Dim Newdate As String
Newdate = "06/10/2015"
Dim Prevdate As String

cn.Open "Provider=sqloledb; " & _
"Data Source=" & "BLD-FS-SQLVS01\PRDINST1, 4020" & ";" & _
"Initial Catalog=" & "HNFS_NetProv" & ";" & _
"Integrated Security=SSPI;"

Set cmd.ActiveConnection = cn
cmd.CommandText = "sp_getDailyStats"
cmd.CommandType = adCmdStoredProc

cmd.Parameters(1) = Newdate


rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
Set rs = cmd.Execute

Prevdate = cmd(2)


Set rs = Nothing
Set cn = Nothing

Ado automatically recognizes that I have two parameters but it is not returning cmd(2)

So how do I get my value to show up? All the examples I see want me to add/create parameters which I don't need to do. I just need to have the paramenter show up but I don't have a way of putting output on the procedure.

Answer 1

There's a bit of confusion here about output parameters and resultsets. You are wanting to capture the value of @myvare that is SELECT'ed out of your procedure; that is not an "out parameter," but a simple result set just as if you'd SELECTed it from a regular table. It should be captured among any other result sets within the "rs" variable in your client program.

In contrast, an output parameter is one defined as one of the parameters to the procedure with an OUTPUT qualifier, and is defined to a SqlCommand object as a SqlParameter with a Direction of Output. You won't get the value of the OUTPUT parameter from the procedure in your client code if you don't define it before execution, then inspect it's value after the procedure returns, something along the lines of the following (after the 1st parameter definition and before the Execute):

SqlParameter prevRunDate = new SqlParameter("@PrevRunDate", SqlDbType.VarChar);
prevRunDate.Direction = ParameterDirection.Output;
prevRunDate.Size = 10;
cmd.Parameters.Add(prevRunDate);