stored procedure and return value

[Jeff]

Hey

..NET 2.0

I have a stored procedure which calculates a value and return that value.

I'm wondering if I should use ExecuteNonQuery, ExecuteScalar or
ExecuteReader on this stored procedure. I want that return value from the
stored procedure. AFAIK I can use ExecuteReader, but maybe the other options
are much better?

any suggestions?

Jeff

 

[Marc Gravell]

Any are fine; the return value is actually used by adding a parameter
to the command with the direction of ReturnValue. After you have
executed the proc, you can obtain the return value via .Value. Note,
however, that if you use ExecuteReader etc (i.e. rows are involved)
then you must walk all the data before you can get out/return values,
since they are positioned at the end of the data-stream.

Marc

 

[Marc Gravell]

I forgot to say, but in general ExecuteNonQuery is a better option if
you don't expect data, simply because it can perform some
optimisations internally. This might have been your real question...

Marc

 

[Jeff]

thanks for replying to my post

What's confuse me with ExecuteNonQuery is that the documentation says that
ExecuteNonQuery returns the number of rows affected by the stored procdeure.
I have a fear that ExecuteNonQuery will return the number of rows instead of
my return value..

any thoughts about this?

 

[Guest]

For me ExecuteScalar will be better, because u can get the return value from
this method rather being depend on output parameter

 

[Nicholas Paldino [.NET/C# MVP]]

Jeff,

Yes, the call to ExecuteNonQuery will return the number of rows
affected, but the return value will be in the parameters collection. If you
want the return value from the stored procedure, you have to access the
Parameters collection on the command, and then access the parameter which
has the return value.

 

[Hans Kesting]

Vivek expressed precisely :

For me ExecuteScalar will be better, because u can get the return value from
this method rather being depend on output parameter

Note: ExecuteScalar will not return the value that is supplied with the
"return" statement in the stored procedure, it will return the first
value of the first row of data. So to use this you need to "select" the
value, not "return" it.

Hans Kesting