Assign Variable Value from Store Procedure

[Steve King]

I've been using the connection execute method to run SQL
Server stored procedures. I would like to be able to
assign a string variable the value of a function or stored
procedure output without having to create an ADODB
recordset to retrieve the value.

The code below uses VB variables as input parameters in
the EXEC statement to return the results of a stored
procedure into a recordset that can be used in VB:

Set PnRS = Cnn.Execute("EXEC spGetPprRecord @PN='" & strPN
& "', @Cage='" & strCage & "'")

I would like to be able to perform something like the
below snippet to assign the return value to the variable
strPprNo.

Dim strPprNo As String
Cnn.Execute ("EXEC " & strPprNo & " =
dbo.fx_GetNextPprNumber @Customer='" & Me.txtCust & "'")