How do I capture a Stored Procedure's "return status" value using ADO (or ODBC)

[Steve van Aardt]

Hello,

I'm currently unable capture the value "returned" from a
Sybase stored procedure using my Excel'97 VBA program can
you help me do this ?

Here's an example of what I've been doing:

In my Sybase 11.9.2 database I have defined the following
stored procedure:

create procedure sp_retvalparam as return 10005141

From a NT Command prompt, I can use "isql" and see the
return value:

1> exec sp_retvalparam
2> go
(return status = 10005141)

but from within the following, I can't seem to access
this "return status":

Function sp_exec1() As Variant
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command

Dim strCon As String

strCon = "DSN=" & DSQUERY & ";DATABASE=" & DB_NAME
& ";UID=" & DB_USER & ";PWD=" & DB_PASSWORD & ";"
cnn.Open strCon

cmd.ActiveConnection = cnn
cmd.CommandText = "sp_retvalparam"
cmd.CommandType = adCmdStoredProc
cmd.Execute

End Function

Does anyone know how I can do this ?

Thanks in advance,

Steve

[Manish Rawat]

Add a return Parameter in command object parameter collection for return
value of a stored procedure
this will always be first in parameter collection

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
dim retval as integer
Set prm = cmd.CreateParameter("returnValue", adInteger, adParamReturnValue)
cmd.Parameters.Append prm
cmd.execute
retval = cmd.parameters(0).value

Manish

"Steve van Aardt" <sva...@hotmail.com> wrote in message
news:995501c16d05$2e10c6c0$b1e62ecf@tkmsftngxa04...