Return value problem

  • Thread starter Thread starter Damien Foggon
  • Start date Start date
D

Damien Foggon

Struggling with a return value problem from a stored procedure. Any one
any ideas?

I have the following stored procedure:

CREATE PROCEDURE [dbo].[stpGetPublisherBooks]
AS
DECLARE @rows int

SELECT Book.BookID, Book.BookTitle, Book.BookMainTopic,
Publisher.PublisherName
FROM Book
INNER JOIN Publisher on Book.BookPublisherID = Publisher.PublisherID
ORDER BY Book.BookTitle

SET @rows = @@ROWCOUNT

RETURN (@rows)

If I execute this in Query Analyzer I get the correct result reuturned
(5 in fact). But if I do the following I always get 0 returned:

// add the return value
SqlParameter myReturnValue = new SqlParameter();
myReturnValue.ParameterName = "@RETURN";
myReturnValue.SqlDbType = SqlDbType.Int;
myReturnValue.Direction = ParameterDirection.ReturnValue;
myCommand.Parameters.Add (myReturnValue);

myConnection.Open();

// execute the command and bind to the DataGrid
SqlDataReader myReader = myCommand.ExecuteReader();
DataGrid1.DataSource = myReader;
DataGrid1.DataBind();
myReader.Close();

// now get the output parameter
Label1.Text = Convert.ToString(myCommand.Parameters["@RETURN"].Value);
 
Hi Damien,

What happens if you omit dataabinding lines?
// DataGrid1.DataSource = myReader;
// DataGrid1.DataBind();
 
It's still set to zero. I've even tried iterating through the
SqlDataReader manually so that I get to the end of it to see if that was
the problem

SqlDataReader myReader = myCommand.ExecuteReader();
//DataGrid1.DataSource = myReader;
//DataGrid1.DataBind();
while (myReader.Read())
{

}
myReader.Close();

All to no avail.

Damien
 
Hi Damien,

It could be something with @@ROWCOUNT.
What happens if you do
set @rows = 5 instead of = @@rowcount
 
Hi Damien,

From your example it looks like you are setting the text value after the
connection has been closed. Try reading the return values before closing the
connection.

Regards,

Julian
 
Nope. You must close the data reader to get any output parameters or
return values.

The connection is still open and closed later on in the code.

Damien
Julian said:
Hi Damien,

From your example it looks like you are setting the text value after the
connection has been closed. Try reading the return values before closing the
connection.

Regards,

Julian

Struggling with a return value problem from a stored procedure. Any one
any ideas?

I have the following stored procedure:

CREATE PROCEDURE [dbo].[stpGetPublisherBooks]
AS
DECLARE @rows int

SELECT Book.BookID, Book.BookTitle, Book.BookMainTopic,
Publisher.PublisherName
FROM Book
INNER JOIN Publisher on Book.BookPublisherID = Publisher.PublisherID
ORDER BY Book.BookTitle

SET @rows = @@ROWCOUNT

RETURN (@rows)

If I execute this in Query Analyzer I get the correct result reuturned
(5 in fact). But if I do the following I always get 0 returned:

// add the return value
SqlParameter myReturnValue = new SqlParameter();
myReturnValue.ParameterName = "@RETURN";
myReturnValue.SqlDbType = SqlDbType.Int;
myReturnValue.Direction = ParameterDirection.ReturnValue;
myCommand.Parameters.Add (myReturnValue);

myConnection.Open();

// execute the command and bind to the DataGrid
SqlDataReader myReader = myCommand.ExecuteReader();
DataGrid1.DataSource = myReader;
DataGrid1.DataBind();
myReader.Close();

// now get the output parameter
Label1.Text = Convert.ToString(myCommand.Parameters["@RETURN"].Value);
Click to expand...
Click to expand...
 
It still returns 0. The only way that I can get the return value is if
I remove the SELECT and return only a number as @rows.
 
One other suggestion -- make @rows an output paramter instead of
trying to set it as the RETURN value. Also, SET NOCOUNT ON should be
the first statement in the sproc. HTH,

Mary
 
It works fine as an OUTPUT and as this is for a tutorial I'm writing I
need it as a RETURN value. All the MSDN documents say it should work...

Mary said:
One other suggestion -- make @rows an output paramter instead of
trying to set it as the RETURN value. Also, SET NOCOUNT ON should be
the first statement in the sproc. HTH,

Mary

It still returns 0. The only way that I can get the return value is if
I remove the SELECT and return only a number as @rows.

Miha Markic [MVP C#] wrote:

Hi Damien,

It could be something with @@ROWCOUNT.
What happens if you do
set @rows = 5 instead of = @@rowcount
Click to expand...
Click to expand...
Click to expand...
 
Did you try "SET NOCOUNT ON" ?

It works fine as an OUTPUT and as this is for a tutorial I'm writing I
need it as a RETURN value. All the MSDN documents say it should work...

Mary said:
One other suggestion -- make @rows an output paramter instead of
trying to set it as the RETURN value. Also, SET NOCOUNT ON should be
the first statement in the sproc. HTH,

Mary

It still returns 0. The only way that I can get the return value is if
I remove the SELECT and return only a number as @rows.

Miha Markic [MVP C#] wrote:


Hi Damien,

It could be something with @@ROWCOUNT.
What happens if you do
set @rows = 5 instead of = @@rowcount
Click to expand...
Click to expand...
Click to expand...
 
Back
Top