How to return a specific numeric value from SP?

[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
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

 

[WhiteKnight57t]

@@ROWCOUNT

 

[ESquared]

You'd need to ask the VB people about the RecordsAffected property.

But there's no reason you can't do as WhiteKnight57t suggested and do

RETURN @@RowCount

or

SELECT @@RowCount

then all you need is to know how to read return codes or how to read multiple returned recordsets.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[TroyMcClure]

Thanks, I'll try the VB area. The @@rowcount does nothing different that what I'd had, so it must be something with the ADO side fo things.
T

 

[JamesLean]

Why do you not want to use an output parameter?? That's exactly what they're there for!

Use the right method for the job you're doing:

- return values to indicate the status of the SP (succeeded, failed etc.)
- output params to return specific values (as per your situation)

ADO properties like RecordsAffected, as far as I know, cannot be explicitly set. I also am fairly sure that it cannot be used to get the rowcount of a select statement - it only shows how may records were affected by an insert, update or delete.

--James

 

[TroyMcClure]

James,

it only shows how may records were affected by an insert, update or delete

I could have sworn that when read the documentation, it said the last Select statement of the sp is what should be returned to RecordsAffected, so I may have misread.

Yes, I can use an OUT param. And currently I am, but the point is somewhat academic now---I'd like to simplify this and not have to dim params when there's a (supposed) built-in property that's supposed to do that for me. It's just cleaner for my app to not have to have an out param for every sp where it's a simple return of a recordset or whatever and I just want a count, say for initializing a progressbar, and Recordcount does not work on forward/readonly cursors.
--T

 

[ESquared]

@@rowcount works fine on select statements. Try it.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[TroyMcClure]

Esquared,
Yes, but that's not what I'm looking for. I was looking to put *something*--whether it be my own variable, @@rowcount, whatever--into the Return statement and have the RecordsAffected property of the ado .execute method contain that value. I have since learned that this is simply not possible, that property is for the last dml statement. I will use an OUT param and be done with it.
T