Reverse Engineered Stored Procedure with output parameter causes exception when `null` provided instead of `OutputParameter<T>`

[elachlan]

When a stored procedure is reverse engineered and used a call is made to it, providing null for the OutputParameter value, it will cause an exception.

It would be nice if instead of having to pass in a value, I could pass null.

Similar to returnValue, it would be good to check for null:

JobId.SetValue(parameterJobId.Value); // Exception here
returnValue?.SetValue(parameterreturnValue.Value);

Provide steps to reproduce a bug

Reverse engineer an SP with an output parameter. Call it and specify null for the OutputParameter<T> parameter.

Provide technical details

• EF Core Power Tools version: 2.6.577

• Exact Visual Studio version: Visual Studio 2022 17.11.5

• Database engine: SQL Server

• EF Core version in use: (e.g. EF Core 7)

• Is Handlebars templates used: no

• Is T4 templates used: no

• Is a SQL Server .dacpac used: no

Example Procedure:

-- Check if the procedure already exists and drop it if it does
IF OBJECT_ID('dbo.TestProc', 'P') IS NOT NULL
    DROP PROCEDURE dbo.TestProc;
GO

-- Create the stored procedure
CREATE PROCEDURE dbo.TestProc
    @InputParam INT,
    @OutputParam INT OUTPUT
AS
BEGIN
    -- Set the output parameter to the same value as the input parameter
    SET @OutputParam = @InputParam;
END;
GO

Generated Code:

public virtual async Task<int> TestProcAsync(int? InputParam, OutputParameter<int?> OutputParam, OutputParameter<int> returnValue = null, CancellationToken cancellationToken = default)
{
    var parameterOutputParam = new SqlParameter
    {
        ParameterName = "OutputParam",
        Direction = System.Data.ParameterDirection.InputOutput,
        Value = OutputParam?._value ?? Convert.DBNull,
        SqlDbType = System.Data.SqlDbType.Int,
    };
    var parameterreturnValue = new SqlParameter
    {
        ParameterName = "returnValue",
        Direction = System.Data.ParameterDirection.Output,
        SqlDbType = System.Data.SqlDbType.Int,
    };

    var sqlParameters = new []
    {
        new SqlParameter
        {
            ParameterName = "InputParam",
            Value = InputParam ?? Convert.DBNull,
            SqlDbType = System.Data.SqlDbType.Int,
        },
        parameterOutputParam,
        parameterreturnValue,
    };
    var _ = await _context.Database.ExecuteSqlRawAsync("EXEC @returnValue = [dbo].[TestProc] @InputParam = @InputParam, @OutputParam = @OutputParam OUTPUT", sqlParameters, cancellationToken);

    OutputParam.SetValue(parameterOutputParam.Value); // Exception here if OutputParam has null passed in
    returnValue?.SetValue(parameterreturnValue.Value);

    return _;
}

await _context.Procedures.TestProcAsync(1,null, null, cancellationToken);

[ErikEJ]

Please provide a full repro and I will have a look, including code to call the sproc.

[elachlan]

Thank you for the quick response. I have updated the description with an example SP and the generated csharp code.

I have several SPs that output values that may or may not be used. in the cases I don't want to use it, I would expect to pass null.

[ErikEJ]

Could you show an example of usage of the generated code please