Return value problem

[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);

 

[Miha Markic [MVP C#]]

Hi Damien,

What happens if you omit dataabinding lines?
// DataGrid1.DataSource = myReader;
// DataGrid1.DataBind();

 

[Damien Foggon]

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

 

[Miha Markic [MVP C#]]

Hi Damien,

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

 

[Julian Crofton]

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

 

[Damien Foggon]

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

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);

 

[Damien Foggon]

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.

 

[Mary Chipman]

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

 

[Damien Foggon]

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

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

 

[Mary Chipman]

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

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