Output SqlParameter Returns NULL Value When Procedure Assigns a Va

[Guest]

I am encountering the following problem with a call to a stored procedure
from C# code.

The stored procedure looks like this:

ALTER PROCEDURE sproc
@id1 int,
@id2 int,
@trans_date datetime,
@flag1 bit output,
@result int Output
AS
..
..
..
Set @flag1 = (SELECT flag From MyTable
Where id = @id2)
-- A record is found and debugger shows @flag1 has a value
..
..
..
Return 0

The C# code:
sqlCmdData.CommandText = "sproc";
sqlCmdData.Parameters.Clear();
sqlCmdData.Parameters.AddWithValue("@id1", _id1);
sqlCmdData.Parameters.AddWithValue("@id2", _id2);
sqlCmdData.Parameters.AddWithValue("@trans_date", DateTime.Now);
SqlParameter sqlParmFlag = new SqlParameter("@flag1", SqlDbType.Bit);
sqlParmFlag.Direction = ParameterDirection.Output;
sqlCmdData.Parameters.Add(sqlParmFlag);
SqlParameter sqlParmResult = new SqlParameter("@result", SqlDbType.Int);
sqlParmResult.Direction = ParameterDirection.Output;
sqlCmdData.Parameters.Add(sqlParmResult);
SqlDataReader dr = sqlCmdData.ExecuteReader();
int _status = Convert.ToInt32(sqlParmResult.Value); // This works OK
bool flag = (bool)sqlParmFlag.Value; // sqlParmFlag.Value is always NULL

This throws a NULL exception.

Any ideas on what may be happening here?

I would appreciate any help.

Thanks,
Eagle

 

[Guest]

By the way I have tried changing

Set @flag1 = (SELECT flag From MyTable
Where id = @id2)

to

SELECT @flag1 = flag From MyTable
Where id = @id2

I got the same result.

Thanks,
Eagle

 

[Mary Chipman [MSFT]]

Does the stored procedure always return the correct value in terms of
what the client application is expecting? One way to handle the
situation is to explicitly set the output parameter to either 0 or 1.
One option is set a default of NULL when you declare it:

@flag1 bit = NULL OUTPUT

You can then put logic in to test if the flag is null before sending
the output back to the client. The alternative is to create a working
variable, do the SELECT, and then set the flag to either 0 or 1 based
on whether the working variable is null. This is the kind of logic
that is best handled on the server, not in your client code.

-mary

 

[Guest]

No, it does not. It always returns null, but the underlying table column
cannot be null. Something is happening in the SqlParameter/SqlCommand
objects that cause a null value to appear in the calling application.

Thanks,
Eagle.

 

[Mary Chipman [MSFT]]

The optimal solution is to craft your stored procedures so that they
always return values that the client application can easily handle.

-mary

 

[Guest]

I found the source of the problem. The MSDN documentation states that
retrieval of output value should be done after the DataReader is closed:

"While the SqlDataReader is being used, the associated SqlConnection is busy
serving the SqlDataReader, and no other operations can be performed on the
SqlConnection other than closing it. This is the case until the Close method
of the SqlDataReader is called. For example, you cannot retrieve output
parameters until after you call Close."

We have using an int output parameter (@result) to return error codes and I
had been checking that value before processing any result set. A error might
indicate that the data in a returned result set might not be valid and should
either not be processed or discarded. Up to this point there had never been
a problem with this scheme until we needed to return a bit type in an output
parameter. This failed until we closed the DataReader on the client.

 

[Mary Chipman [MSFT]]

I'm glad you got it working - thanks for posting the solution.

-mary