Stored procedure not returning a value in @RETURN_VALUE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyon,

I am experiencing a problem with a stored procedure not returning a value in
the parameter @RETURN_VALUE when debugging in C#.

The stored procedure looks something like this:

CREATE PROCEDURE dbo.ListSomething

@myParam int

AS

declare @rc int
set @rc = 0

select * from dbo.myTable where myField = @myParam

if (@@error <> 0) set @rc = @@error
return @rc

When I run this stored procedure in Query Analyzer, it works as expected
returning any matching records and a return value of 0. For information, the
SQL I'm using in Query Analyzer is:

declare @rc int
exec @rc = ListSomething 123
select @rc

In my C# code though, when I query the @RETURN_VALUE parameter in the
Parameters collection of the SqlCommand, all I ever get is "undefined value".
Even if I change the stored procedure to return something specific such as 1,
2, 3, etc. The code I'm using is:

SqlConnection DBConnection = new SqlConnection();
SqlCommand DBCommand = new SqlCommand();

DBConnection.ConnectionString = "Persist Security Info=False;Integrated
Security=SSPI;database=myDatabase;server=myServer";
DBConnection.Open();

DBCommand.Connection = DBConnection;
DBCommand.CommandText = "ListSomething";
DBCommand.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(DBCommand);

if (DBCommand.Parameters.Count > 1)
{
foreach (SqlParameter SPParameter in DBCommand.Parameters)
{
// set the value of each input parameter
}
}

SqlDataReader CommandResults =
DBCommand.ExecuteReader(CommandBehavior.CloseConnection);

if (DBCommand.Parameters["@RETURN_VALUE"].Value == 0)
{
// display results from stored procedure
}
else
{
// raise an error
}

CommandResults.Close();

I have read that the use of DeriveParameters is not recommended because of
the additional roundtrip to the server, but this isn't an issue for me as the
stored procedures are not going to be used that often, and I need a way of
dynamically building the parameters list for population without having to
define all of them in advance.

When I debug the code, the line that reads the value of @RETURN_VALUE fails
as it has an "undefined value". The return value just isn't coming back from
the stored procedure, even though through Query Analyzer it is being returned.

Is anyone able to help me get the return value from my stored procedure
please? I've searched the internet but haven't had much luck.

Many thanks in advance
Adam Ainger
 
If I may quote the Bill Jedi Master Vaughn , Himself
"If you want to capture the integer passed back by the RETURN statement in
TSQL or your stored procedure's OUTPUT parameters, you're going to have to
take another few minutes to setup a Command object to capture these values
from the resultset. Again, and I can't emphasis this enough, these values
are not made available until you've processed all rowsets in each resultset
(if there are any). Yes, a stored procedure can return several resultsets,
but the RETURN value and OUTPUT parameters are not populated until all
queries have been completed and all rowsets transmitted back to the client.
"

ExecuteReader doesn't process all of the results, it just buffers up a few.
The return value is sittng at the end of the stream so it's not available.
Try running through the reader

while(rdr.Read()){
}
And then check it, it should be there (unless you have multiple resultsets,
then you'll need to nest them in a .Next loop as well.

I highly recommend reading his article on GP - it's a great read -
http://www.betav.com -> Articles ->MSDN -> Retrieving the Gozoutas.

Just make sure you don't ask him if there's more than one way to skin a cat.
--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
Adam Ainger said:
Hi everyon,

I am experiencing a problem with a stored procedure not returning a value in
the parameter @RETURN_VALUE when debugging in C#.

The stored procedure looks something like this:

CREATE PROCEDURE dbo.ListSomething

@myParam int

AS

declare @rc int
set @rc = 0

select * from dbo.myTable where myField = @myParam

if (@@error <> 0) set @rc = @@error
return @rc

When I run this stored procedure in Query Analyzer, it works as expected
returning any matching records and a return value of 0. For information, the
SQL I'm using in Query Analyzer is:

declare @rc int
exec @rc = ListSomething 123
select @rc

In my C# code though, when I query the @RETURN_VALUE parameter in the
Parameters collection of the SqlCommand, all I ever get is "undefined value".
Even if I change the stored procedure to return something specific such as 1,
2, 3, etc. The code I'm using is:

SqlConnection DBConnection = new SqlConnection();
SqlCommand DBCommand = new SqlCommand();

DBConnection.ConnectionString = "Persist Security Info=False;Integrated
Security=SSPI;database=myDatabase;server=myServer";
DBConnection.Open();

DBCommand.Connection = DBConnection;
DBCommand.CommandText = "ListSomething";
DBCommand.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(DBCommand);

if (DBCommand.Parameters.Count > 1)
{
foreach (SqlParameter SPParameter in DBCommand.Parameters)
{
// set the value of each input parameter
}
}

SqlDataReader CommandResults =
DBCommand.ExecuteReader(CommandBehavior.CloseConnection);

if (DBCommand.Parameters["@RETURN_VALUE"].Value == 0)
{
// display results from stored procedure
}
else
{
// raise an error
}

CommandResults.Close();

I have read that the use of DeriveParameters is not recommended because of
the additional roundtrip to the server, but this isn't an issue for me as the
stored procedures are not going to be used that often, and I need a way of
dynamically building the parameters list for population without having to
define all of them in advance.

When I debug the code, the line that reads the value of @RETURN_VALUE fails
as it has an "undefined value". The return value just isn't coming back from
the stored procedure, even though through Query Analyzer it is being returned.

Is anyone able to help me get the return value from my stored procedure
please? I've searched the internet but haven't had much luck.

Many thanks in advance
Adam Ainger
Click to expand...
 
Hi Bill,

I thought that Bill speaks much better English than "the" Jedi Master, am I
wrong in that?

Cor
 
Many thanks for your help William - I'm now getting the values I'm expecting
from the stored procedure.

Regards
Adam Ainger

W.G. Ryan eMVP said:
If I may quote the Bill Jedi Master Vaughn , Himself
"If you want to capture the integer passed back by the RETURN statement in
TSQL or your stored procedure's OUTPUT parameters, you're going to have to
take another few minutes to setup a Command object to capture these values
from the resultset. Again, and I can't emphasis this enough, these values
are not made available until you've processed all rowsets in each resultset
(if there are any). Yes, a stored procedure can return several resultsets,
but the RETURN value and OUTPUT parameters are not populated until all
queries have been completed and all rowsets transmitted back to the client.
"

ExecuteReader doesn't process all of the results, it just buffers up a few.
The return value is sittng at the end of the stream so it's not available.
Try running through the reader

while(rdr.Read()){
}
And then check it, it should be there (unless you have multiple resultsets,
then you'll need to nest them in a .Next loop as well.

I highly recommend reading his article on GP - it's a great read -
http://www.betav.com -> Articles ->MSDN -> Retrieving the Gozoutas.

Just make sure you don't ask him if there's more than one way to skin a cat.
--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
Adam Ainger said:
Hi everyon,

I am experiencing a problem with a stored procedure not returning a value in
the parameter @RETURN_VALUE when debugging in C#.

The stored procedure looks something like this:

CREATE PROCEDURE dbo.ListSomething

@myParam int

AS

declare @rc int
set @rc = 0

select * from dbo.myTable where myField = @myParam

if (@@error <> 0) set @rc = @@error
return @rc

When I run this stored procedure in Query Analyzer, it works as expected
returning any matching records and a return value of 0. For information, the
SQL I'm using in Query Analyzer is:

declare @rc int
exec @rc = ListSomething 123
select @rc

In my C# code though, when I query the @RETURN_VALUE parameter in the
Parameters collection of the SqlCommand, all I ever get is "undefined value".
Even if I change the stored procedure to return something specific such as 1,
2, 3, etc. The code I'm using is:

SqlConnection DBConnection = new SqlConnection();
SqlCommand DBCommand = new SqlCommand();

DBConnection.ConnectionString = "Persist Security Info=False;Integrated
Security=SSPI;database=myDatabase;server=myServer";
DBConnection.Open();

DBCommand.Connection = DBConnection;
DBCommand.CommandText = "ListSomething";
DBCommand.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(DBCommand);

if (DBCommand.Parameters.Count > 1)
{
foreach (SqlParameter SPParameter in DBCommand.Parameters)
{
// set the value of each input parameter
}
}

SqlDataReader CommandResults =
DBCommand.ExecuteReader(CommandBehavior.CloseConnection);

if (DBCommand.Parameters["@RETURN_VALUE"].Value == 0)
{
// display results from stored procedure
}
else
{
// raise an error
}

CommandResults.Close();

I have read that the use of DeriveParameters is not recommended because of
the additional roundtrip to the server, but this isn't an issue for me as the
stored procedures are not going to be used that often, and I need a way of
dynamically building the parameters list for population without having to
define all of them in advance.

When I debug the code, the line that reads the value of @RETURN_VALUE fails
as it has an "undefined value". The return value just isn't coming back from
the stored procedure, even though through Query Analyzer it is being returned.

Is anyone able to help me get the return value from my stored procedure
please? I've searched the internet but haven't had much luck.

Many thanks in advance
Adam Ainger
Click to expand...
Click to expand...
 
Back
Top