CODEWITHSUNDEEP
c#sql-serverado.netintout
Chandra Maral
Chandra Maral

Reputation: 49

Integer out parameter null issue in ADO.NET while calling through procedure

I'm facing one issue when I try to get output value from SQL Server stored procedure which is of INT type, then I'm getting NULL value ☹ .

Here is the stored procedure:

CREATE PROCEDURE dbo.NULLISSUE 
    @input VARCHAR(10),
    @output INT = 0 OUTPUT
AS
BEGIN
    IF @input >= '1'
    BEGIN
        SET @output = @output + 1

        RETURN(0)
    END
END

Here is the .NET code:

using (var connection = new SqlConnection(connectionString))
{
    using (var command = new SqlCommand())
    {
        command.Connection = connection;
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "dbo.NULLISSUE";

        command.Parameters.Clear();
        command.Parameters.AddWithValue("@input", "1");

        command.Parameters.AddWithValue("@output", SqlDbType.Int);
        command.Parameters["@output"].Value = 0;
        command.Parameters["@output"].Direction = ParameterDirection.Output;

        command.CommandTimeout = 3000;

        connection.Open();
        command.ExecuteNonQuery();
        connection.Close();

        Console.WriteLine(command.Parameters["@output"].Value);
        //null value ?
        Console.ReadLine();
    }
}

I have a fix by doing ISNULL(@output, 0) in the procedure but not sure why it can be done on the .NET side and why ADO.NET not passing/setting/initializing OUTPUT parameter = 0.

Thanks in advance.

Upvotes: 1

Views: 903

Answers (1)

Alex K.
Alex K.

Reputation: 175876

@output must be ParameterDirection.InputOutput if you want it to use the initial value set within the client.

Currently as it's ParameterDirection.Output the value is ignored, it defaults to NULL within the procedure and NULL + anything results in a NULL.

Upvotes: 4

Related Questions