ADO and Stored Procedures

[Arun Narayanan]

How do you call a SQL stored procedure from VB6.0 using ADO. The stored procedure has one input and one output parameter.

thanks
-Arun

[JHausmann]

The following should work for sp's with input and output parameters:


[code]
dim parm as parameter
dim cmd as new Command
dim rs as new Recordset

'make sure you define your connection information


cmd.CommandText="Stored_Procedure_name"
cmd.CommandType = adCmdStoredProc
'the second parameter defines the data type, in this example it's char for a length of 10 (third parameter is length)
set parm = cmd.CreateParamter("Name", adChar, 10, param_value_to_pass)
cmd.parameters.Append parm

set rs = cmd.Execute
if not rs.eof the
do while not rs.eof
return_value= rs.fields(0)
rs.MoveNext
loop
end if

[Arun Narayanan]

Thanks!!!
What if the SP does not return a recordset but just a value like '100'. Do we still need to use the recordset object.

Do you specify the second parameter ( output ) as adParamOutput or adParamReturnValue?

-Arun

[Glenn]

If your just returning a value you don't need the recordset.
Simply define it as a second parameter. The choice to use
adParamOutput vs adParamReturnValue depends upon how the stored procedure is written.

If you use adParamOutput then the value must be defined in the parameter list at the beginning of the stored procedure:

Example:
Param integer OUTPUT

The OUTPUT keyword is required to let SQL know that it is an output parameter.

If you use adParamReturnValue then the value will be the value that is returned by the "Return" statement in your stored procedure:

Example:
Return (100)

Note: you can only return intger using the return statement

If you use either of the methods you only have to execute the ado command object.

Hope this helps

[Arun Narayanan]

I did write my stored procedure with the output keyword. This is how the definition looks like.

create procedure sp_getbatchnumber(@batch_type varchar(20) = null, @batch_number char(3) output)
as
;;;;;;
;;;;;;
;;;;;;;
select @batch_number = '100'
return

My VB code looks like this:

'input parameter
strPrmName = "batch_type"
Set ADOprm = ADOcmd.CreateParameter(strPrmName, adVarChar, adParamInput, 20, "fax")
ADOcmd.Parameters.Append ADOprm
'output parameter
strPrmName = "batch_number"
Set ADOprm = ADOcmd.CreateParameter(strPrmName, adChar, adParamOutput, 3)
ADOcmd.Parameters.Append ADOprm

Set ADOrs = ADOcmd.Execute



Whats happening is the ADOcmd.Parameters("batch_number").Value contains "Empty" value and not the one returned by SQL.


Any ideas whats happening ?

[Serge]

You don't need a recordset to get the value from the parameter. Try something like this:

Dim cn As New ADODB.Connection
Dim cm As New ADODB.Command
Dim parInput As NEW ADODB.Parameter
Dim parOutput As NEW ADODB.Parameter

cn.Open "DSN=MyDSN"
Set cm.ActiveConnection = cn

cm.CommandText = "sp_MyProc"
cm.CommandType = adCmdStoredProc

Set parInput = cm.CreateParameter("Name", adChar, 10, "MyValue")
cm.Parameters.Append parInput

Set parOutput = cm.CreateParameter("OutPut", adInteger, adParamOutput)
cm.Parameters.Append parOutput

cm.Execute

MsgBox "Returned Value is " & parOutput.Value

cn.Close
Set parInput = Nothing
Set parOutput = Nothing
Set cm = Nothing
Set cn = Nothing