[Resolved] Return value of Stored Procedure

[PilgrimPete]

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

[naruponk]

Thanks a lot for help guys

Even i set Return @Result at a first line of Stored Procedure
I still getting same error "....When object is closed"
The problem might be my Stored Procedure

Decalre @Result

SET @Result=99
Return @Result

........ below is too much of proceed ...........
............................................................

If my Stored Procedure is look like this the process will end at
Return @Result?

Can i use Return keyword without select keyword before?

I have found that after EXECUTE Other_StoredProc
I will unable to open a recordset even i use select keyword,
so .... how can i retrive a recordset?

[PilgrimPete]

If you have a return statement at the top of your procedure, then it will do just that: Return.
Nothing after the 'Return' will get executed. So, in your latest example there is no recordset created, so none will be returned.

In order to get at your return value, you'll need to use the example that techgnome posted.

I'm still not sure why you want a recordset and a return parameter containing the same value...

[naruponk]

I'm still not sure why you want a recordset and a return parameter containing the same value...

I just want to be able to retreive a value which is return from stored procedure and using ADO to retreive it. (Return value is after execute Other_StoredProc line in Stored Proc)

Originally Posted by techgnome
cmd.Parameters.Append cmd.CreateParameter("@ReturnValue",adInteger,adParamReturnValue)

I try your code but i got another error "Formal parameter @Result was defined as OUTPUT but the actual parameter not declared OUTPUT."

Can you tell me how should i declare a variable in my stored procedure
to able to return a value by using ADO

Thanks everyone