ADO: How to get the value of RETURN from stored proc

[Paul Z]

I want to get the value of RETURN from stored proc but i don't know how.
Help, please.

Pail V. Zorin
zpv73@yahoo.com

[Johnny101]

I'm not sure about Oracle, but I assume it's pretty similar, this is for SQL Server:

Dim cn as ADODB.Connection
Dim cmd as ADODB.Command
Dim lRetVal as Long

set cn = new ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB;Server=MyServer;User ID=me;Password=mine;"
cn.Open

set cmd = new ADODB.Command
With cmd
.CommandText = "Name of Stored Procedure Here"
.CommandType = adCmdStoredProc
.CommandTimeout = 600 '10 minutes
.Parameters.Append .CreateParameter("RecordCount", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@OrderNumber", adInteger, adParamInput)
.Parameters("@OrderNumber") = iOrderNum
End With

cmd.Execute

'Now check the return status of the proc...
lRetVal = cmd.Parameters("RecordCount")

If lRetVal > 0 then
...




NOTE The ReturnValue parameter MUST be the first one declared, all other input/output parameters come after this one. This return value is populated by the stored proc by the "RETURN" command (in SQL Server). This is a typica example of the stored proc above:

CREATE PROCEDURE sp_MyProcedure (@OrderNum int)
as

BEGIN

DECLARE @RowCount

SELECT @RowCount = COUNT(*)
FROM MyOrderTable (nolock)
WHERE OrderNumber = @OrderNum

RETURN @RowCount

END




Hope this helps,

John


John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[Paul Z]

Thanks a lot.


Pail V. Zorin
zpv73@yahoo.com

[peppe]

Now, how do you do it C++?

Thanks.