Output param set to DBNull

[Eugene Banks]

I am using a stored procedure on SQL server to test for a few
conditions, setting a BIT depending on the result of each test. Each
BIT parameter is defined as an output param in the stored procedure and
set to 0 by default. From my code, I define each parameter, add them to
the SqlCommand object, and then ExecuteNonQuery(). When I check the
output paramaters on my SqlCommand object, they now have a value of
DBNull, even though they are initialized to 0 by the procedure. When I
run the stored procedure from a query window, the sp behaves as
expected. Please help!


Stored procedure snippit
--------------------------------------
@ParticipantExists BIT = 0 OUTPUT

-- Check for valid Participant
IF EXISTS (SELECT p.ind_id
FROM Participant p, Employer e
WHERE e.alias = @Co_Code
AND e.employer_id = p.employer_id
AND p.external_id = @Employee_Identifier)
BEGIN
SET @IndividualID = (SELECT p.ind_id
FROM Participant p, Employer e
WHERE e.alias = @Co_Code
AND e.employer_id = p.employer_id
AND p.external_id = @Employee_Identifier)
SET @ParticipantExists = 1
END



C# Code snippit
---------------------------------------
cmd = new SqlCommand("usp_SelectParticipantVerification", conn, tran);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter parm = new SqlParameter("@ParticipantExists", SqlDbType.Bit);
parm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parm);
cmd.ExecuteNonQuery();



cmd.Parameters["@ParticipantExists"].Value is now DBNull. Any ideas?

Thanks!

 

[Eugene Banks]

I should also point out that I experience the same conditions even if I
change the SQL parameter type. If I define the SqlParameter direction
as InputOutput and give it an initial value, the SqlParamter retains its
initialized value. Not every output param is affected, I can find no
reason for the one or two output params that are affected.

Any ideas?

 

[Eugene Banks]

I should also point out that I experience the same conditions even if I
change the SQL parameter type. If I define the SqlParameter direction
as InputOutput and give it an initial value, the SqlParamter retains its
initialized value. Not every output param is affected, I can find no
reason for the one or two output params that are affected.

Any ideas?

 

[Grzegorz Danowski]

I am using a stored procedure on SQL server to test for a few conditions,
setting a BIT depending on the result of each test. Each BIT parameter is
defined as an output param in the stored procedure and set to 0 by default.
From my code, I define each parameter, add them to the SqlCommand object,
and then ExecuteNonQuery(). When I check the output paramaters on my
SqlCommand object, they now have a value of DBNull, even though they are
initialized to 0 by the procedure. When I run the stored procedure from a
query window, the sp behaves as expected. Please help!


Stored procedure snippit
--------------------------------------
@ParticipantExists BIT = 0 OUTPUT

-- Check for valid Participant
IF EXISTS (SELECT p.ind_id
FROM Participant p, Employer e
WHERE e.alias = @Co_Code
AND e.employer_id = p.employer_id
AND p.external_id = @Employee_Identifier)
BEGIN
SET @IndividualID = (SELECT p.ind_id
FROM Participant p, Employer e
WHERE e.alias = @Co_Code
AND e.employer_id = p.employer_id
AND p.external_id = @Employee_Identifier)
SET @ParticipantExists = 1
END



C# Code snippit
---------------------------------------
cmd = new SqlCommand("usp_SelectParticipantVerification", conn, tran);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter parm = new SqlParameter("@ParticipantExists", SqlDbType.Bit);
parm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parm);
cmd.ExecuteNonQuery();



cmd.Parameters["@ParticipantExists"].Value is now DBNull. Any ideas?

I have tested your code on simple stored proc:
Create Procedure usp_SelectParticipantVerification
@ParticipantExists BIT = 0 OUTPUT
As
Set @ParticipantExists = 1

And it works ok.
I wonder Why it doesn't work in your app. Maybe open transaction? Test it
temporally removing transation.
Use return value instead of output parameter. Or, another idea, change sp
to:

Create Procedure usp_SelectParticipantVerification
As
--....
Select Cast(1 As Bit)

And then in c#:
cmd = new SqlCommand("usp_SelectParticipantVerification", conn, tran);
cmd.CommandType = CommandType.StoredProcedure;
bool t = (bool)cmd.ExecuteScalar()

I hope it helps in some way.
Regards,
Grzegorz

 

[WJ]

-- Check for valid Participant

IF EXISTS (SELECT p.ind_id
FROM Participant p, Employer e
WHERE e.alias = @Co_Code
AND e.employer_id = p.employer_id
AND p.external_id = @Employee_Identifier)
BEGIN
SET @IndividualID = (SELECT p.ind_id
FROM Participant p, Employer e
WHERE e.alias = @Co_Code
AND e.employer_id = p.employer_id
AND p.external_id = @Employee_Identifier)
SET @ParticipantExists = 1
END

else set @ParticipantExists=0

return @ParticipantExists

*** It works for me.

John