Quote Originally Posted by szlamany
Originally Posted by techgnome
Nargh... Either SELECT the value or RETURN it... no need to do both...
This is really good advice - as was adding the SET NOCOUNT ON - which is one of our SQL Rules to Live by.

RECORDSET data and output parameter data is in two different objects in ADO - and they cannot be touched at the same time.

You have to process RS, then look at output parameters, then look at RS (if you have more than one RECORDSET in the SPROC), then look at output parameters again.

SET NOCOUNT ON will make sure that the annoying "1 row affected" message doesn't get in the way of RECORDSET processing or output parameter processing.
I agree totally... I was just trying to answer what I thought was a hypothetical question, and to prove that given the posted code, it would actually work.
I'd never actually advocate the use of a recordset to return a single value; though I generally lean towards using an output parameter to return data values, and save the adParamReturnValue parameter to return the success (or otherwise) of my proc - not least because you are restricted to returning ints in a RETURN statement. However, that's just down to personal taste and coding standards I guess...