How to get ADO .Execute method to return number from SQL sp? 1

[TroyMcClure]

Hi,
In a stored procedure I want to return a certain calculated number from that sp to a VP app that uses an ADO Command.Execute to run it.

I want to return this value to the 'RecordsAffected' argument of the .Execute command (I do NOT want to use an OUT param)
My last statement in the SP is:
Return @thenumber /* This value is *NOT* what I see returned
Go

The number is not returned. The number happens to be the number of recs in one of the tables that's dealt with in the sp, so I tried having the last statement be:
Select * From TheTable
...and this doesn't work either. I had read that the RecordsAffected arg will return the count of the last SQL statement of the sp that it called, or if the .Execute is a simple sql statement and not a stored procedure, it will just return that record count. This does not seem to be the case.

Can anyone tell me how to return a specific number to the RecordsAffected property?
Thanks,
T

 

[BogdanMBM]

MyADOConnection.Execute "Decleare @MySQLReturnVar int; Select @MySQLReturnVar = MyStoredProcedure"

But you must use an ADO recordset to "capture" the value of @MySQLReturnVar!
Something like

SET MyADORecordset = MyADOConnection.Execute "Decleare @MySQLReturnVar int; Select @MySQLReturnVar = MyStoredProcedure"
print MyADORecordset.Fields(0) ' or trie Fields(1), don't remember well... :)

And in your stored procedure you have to have Return YourNumber.
This "YourNumber" will be passed to @MySQLReturnVar and further to VB.

Hope I've been helpful,
Bogdan Muresan.

 

[BogdanMBM]

Sorry, I ment:

SET MyADORecordset = MyADOConnection.Execute "Decleare @MySQLReturnVar int; Select @MySQLReturnVar = [COLOR=#ff0000]Execute [/color] MyStoredProcedure"
print MyADORecordset.Fields(0) ' or trie Fields(1), don't remember well... :)

Hope I've been helpful,
Bogdan Muresan.

 

[bjd4jc]

If you wish to avoid the use of a recordset then use the command object. Here is some code to do this:

    Dim cmd As ADODB.Command
    
    With cmd
        .ActiveConnection = con
        .CommandType = adCmdStoredProc
        .CommandText "SP_Name"
        
        .Parameters.Append .CreateParameter("@ReturnValue", adInteger, adParamReturnValue)
        'Add other params as necessary
    End With
    
    cmd.Execute
    
    MsgBox cmd.Parameters("@ReturnValue").Value

 

[TroyMcClure]

Bogdan
I don't want a recordset, I know I can return that as the 'return type', I want to use the built-in "RecordsAffecsted" property of the .Execute method of the command object.

,bjd4c,
As with the response from Bogdan, I want to use the built-in "RecordsAffecsted" property of the .Execute method of the command object--not an OUT parameter. I know I can use either of those and get what I want--just as I can always run an ado count() query on the table after the sp is done. But that's the point--why complicate things when there is supposed to be a built-in property (RecordsAffected) that MS's documentations says should return the rowcount of last SELECT executed in the sp.

So it boils down to that question: What exactly does the RecordsAffected property return if not what the docs say it should? In practice, it seems to return the *previous* count of records in the table that I want to get the count from.

For example, The stored procedure loads a table with 9 records, the RecordsAffected returns 0. Next iteration, the sp loads 50 records, the RecordsAffected shows 9, Next iteration it loads 35, RecordsAffected returns 50--and so on. Now, in the sp, I delete the records from the previous run of the sp, so maybe it's returning the last DML or Action sql statement, not the last Select?

-T

 

[bjd4jc]

I don't understand why you are being so paticular as to have to get it from the records affected property...

I don't see anywhere indicated in the documentation that the RecordsAffected property has the value of the return value of the stored procedure.

>>Now, in the sp, I delete the records from the previous run of the sp, so maybe it's returning the last DML or Action sql statement, not the last Select

This behavior is document in

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

You can not plop your own value into the RecordsAffected property. This property is only populated by INSERT, UPDATE AND DELTE commands.

 

[TroyMcClure]

bjd4jc,
Thanks that does clear it up. I would have sworn I read that it said the last Select statement was what was returned, but it's only the action statements, that was my confusion.
-T

 

[BogdanMBM]

Heh! Sorry again. In fact, the correct statement is

SET MyADORecordset = MyADOConnection.Execute "Decleare @MySQLReturnVar int; [blue] Execute [/blue] @MySQLReturnVar =  MyStoredProcedure"

But the bjd4jc's solution is nicer then mine. It uses less memoty (doesn't use that recordset...). A star from me. For me it was usefull.

 

[TroyMcClure]

Now I'm really confused.
Here is what I have at the most basic level:

CREATE PROCEDURE ReturnRecset (@iRecCount int output)
as
set nocount off
SELECT * FROM tblTest WHERE tMachine = @machine
Order By tMachine, tsortid
set @iRecCount = isnull(@@RowCount,0)
GO

Now, in VB:

(assume command, connection,params are correctly set up, with pRecs being adInteger, adParamOutput)

dim rs as adodb.recordset,LTotalRecs as long
set rs = cmdRecs.Execute
lTotalRecs = pRecs.Value

The recordset is FINE--it returns the records but pRecs is Empty

Why??
Now, if I simply do:

cmdRecs.Execute
lTotalRecs = pRecs.Value

Then lTotalrecs has the correct value but of course I have no recordset!!

I want to execute a stored procedure and in one .Execute have the recordset along with the correct # of records.
Is this possible?
Thanks,
T