stored procedure with output variable

[ExtraD]

Hi,

i made an stored procedure with output variables. I succeeded to execute a normal procedure but how can I read the return value?
This is how I do it:

Dim query As String
Dim rs As ADODB.Recordset
Dim rsFlex As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter

'!' update with a Stored Procedures
Set cmd = New ADODB.Command
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cnSQLFCC
cmd.CommandText = "output"

Set param = cmd.CreateParameter("@pType", adVarChar, adParamInput, 6, "A")
cmd.Parameters.Append param
Dim tAdm As String
Set param = cmd.CreateParameter("@pAdm", adVarChar, adParamInput, 6, "B")
cmd.Parameters.Append param

cmd.Execute


thanx in advance

 

[fredericofonseca]

a SP should either return output parameters or return a recordset.

If the first you MUST declare the output parameters when you are declaring your input ones.

If the second you need to assign the execution of the cmd object to a recordset object and then loop through it.

e.g.
dim rs as recordset
...
set rs = cmd.execute (or similar).

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BobRodes]

There's a distinction between a return value and an output parameter that has to be made as well. Check out the following doc:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthcreateparam.asp.

In particular, look at the ParameterDirectionEnum type, which has adParamInput, adParamOutput, and adParamReturnValue. Which ones you use depends on which ones your stored proc is expecting.

Clearly, however, you have defined only input parameters, so it stands to reason you're not getting any output parameters or return values.

HTH

Bob

 

[ExtraD]

i declared the last parameter as output:
Set param = cmd.CreateParameter("@pAdm", adVarChar, adParamOuput, 6, tAdm)
cmd.Parameters.Append param

but where is the output parameter stored?
this doesn't work:
Dim output As String
output = cmd.Execute
MsgBox output

thanx

 

[ExtraD]

Set cmd = New ADODB.Command
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cnSQLFCC
cmd.CommandText = "output"

Set param = cmd.CreateParameter("@pType", adVarChar, adParamInput, 6, "A")
cmd.Parameters.Append param
Dim tAdm As String
tAdm = ""
Set param = cmd.CreateParameter("@pAdm", adVarChar, adParamOutput, 6, tAdm)
cmd.Parameters.Append param


'Dim output As String
'output = cmd.Execute
'MsgBox output

cmd.Execute
MsgBox tAdm

 

[BobRodes]

With an output parameter, you run the stored proc, which has a similarly defined output parameter. (The parameters collection must match the parameters in the stored proc.) So, you run the stored proc, and the output parameter in the parameters collection will contain whatever value was assigned to the corresponding output parameter in the stored proc.

Does that help?

Bob

 

[Sheco]

after execution try this:
[tt]debug.print cmd.Parameters("@pAdm")[/tt]

 

[ExtraD]

Thanx Sheco that whas the answer i was looking for