Return value from stored procedure questions

[Guest]

I have a question regarding accessing a stored procedure's "return" value. Some of my SQL Server stored procedures return a result set. The only way I can see what the stored procedure return in the "return" value is by using the .ExecuteNonQuery command method. If I use the .ExecuteReader method the "return" value is always 0. My questions are

1. What is the syntax to see what the "return" value is from a stored procedure if I use the ExecuteReader method? (assuming I expect the sp to return a result set
2. What is the cost of performing both an ExecuteNonQuery (to get the return value) and ExecuteReader (to get the result set) on the same command object

Code examples

1. Using the ExecuteReader method that never returns the correct sp "return" value (I even changed the sp to always return a positive value)

set up the connection, command, et
command.Parameters.Add("@return", SqlDbType.Int
command.Parameters("@return").Direction = ParameterDirection.ReturnValu
connection.Open(
command.ExecuteReader(

If (command.Parameters("@return").Value = 0) Then '0 means no error, at this point even if I force the return value to 1 the If statement is always tru

2. Cost of using the .ExecuteNonQuery and .ExecuteReader() methods, this works but I'm concerned about cost and performance

set up the connection, command, et
command.Parameters.Add("@return", SqlDbType.Int
command.Parameters("@return").Direction = ParameterDirection.ReturnValu
connection.Open(
command.ExecuteNonQuery(

If (command.Parameters("@return").Value = 0) The
reader = command.ExecuteReader(

 

[Mary Chipman]

Have you explicitly set a return value in the stored procedures?
Generally speaking, stored procedures that return result sets return
all information in the result set, and don't also have output
parameters or explicitly set a value with the T_SQL RETURN statement.
If the stored procedure doesn't have a RETURN <int> statement, then
the return value is always going to be 0. See "Using RETURN" in SQL
Books Online for more info.

--Mary

 

[Guest]

Yes, I set the stored procedure's return value with the RETURN statement. Typically I check to see if there was an error running the query within the sp via the SELECT @errno = @@ERROR and then test to see if @errno <> 0, if true then I RETURN(1), otherwise I RETURN(0). In my test I set both RETURN statements to pass (1) back, the value did not get updated in my Command object as 1, it remained 0

----- Mary Chipman wrote: ----

Have you explicitly set a return value in the stored procedures
Generally speaking, stored procedures that return result sets retur
all information in the result set, and don't also have outpu
parameters or explicitly set a value with the T_SQL RETURN statement
If the stored procedure doesn't have a RETURN <int> statement, the
the return value is always going to be 0. See "Using RETURN" in SQ
Books Online for more info

--Mar

On Mon, 12 Apr 2004 06:56:04 -0700, "Anthony Reynolds

 

[David Sceppa]

If your stored procedure returns a resultset, you must close
the resultset before accessing the value of the return or output
parameters. Think of it this way, if your stored procedure
contains the following:

SELECT ... FROM MyTable WHERE ...
RETURN 42

You have to process the data SQL Server returned for your SELECT
query before moving on to the call to RETURN.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.

 

[Mary Chipman]

Just to add to what David said -- be aware that that by creating
stored procedures that return result sets AND parameters that you are
also adding an additional round trip to the server for each and every
call. You might want to rethink that strategy in the interests of
network efficiency.

--mary