Optional Parameters in Stored Procs

[NeilN1]

Not sure if I can do this so asking here. Stored proc modifications are a regular thing and sometimes new arguments are added as optional so existing code can run unmodified. The stored proc:

CREATE PROC [dbo].[usp_ReferralGet](@referralId UNIQUEIDENTIFIER = NULL, @contactId UNIQUEIDENTIFIER = NULL, @fromDate DATETIME = NULL, @toDate DATETIME = NULL)

generates:

Task<List<UspReferralGetReturnModel>> UspReferralGetAsync(Guid? referralId, Guid? contactId, DateTime? fromDate, DateTime? toDate);

which means any call to that interface method has to change to explicitly pass all newly added arguments.

Is there a way to have default values generated for arguments so something like this is produced?

Task<List<UspReferralGetReturnModel>> UspReferralGetAsync(Guid? referralId = null, Guid? contactId = null, DateTime? fromDate = null, DateTime? toDate = null);

[sjh37]

Omg, I can't believe I've never added that functionality before. Thanks @NeilN1 I'll get that added.

[NeilN1]

Hi @sjh37 - is there any targeted implementation date for this?

[sjh37]

@NeilN1 I can have it done for you today

Each stored procedure gets two versions, one with and one without the procResult.

public List<SalesByYearReturnModel> SalesByYear(DateTime? beginningDate, DateTime? endingDate)
{
    int procResult;
    return SalesByYear(beginningDate, endingDate, out procResult);
}

public List<SalesByYearReturnModel> SalesByYear(DateTime? beginningDate, DateTime? endingDate, out int procResult)
{
    // Call SP
}

Note

If I change a stored proc to include nulls:

public List<SalesByYearReturnModel> SalesByYear(DateTime? beginningDate = null, DateTime? endingDate = null)
{...}

public List<SalesByYearReturnModel> SalesByYear(
    DateTime? beginningDate = null,
    DateTime? endingDate = null,
    out int procResult) // <<<--- illegal, and cannot supply default value for `out` parameters

The second call, which includes a procResult, cannot be at the end of the optional parameters. It must be at the beginning:

public List<SalesByYearReturnModel> SalesByYear(
    out int procResult,
    DateTime? beginningDate = null,
    DateTime? endingDate = null)

Which would change its signature.

I'll make the change as it's a reasonable request and only hits people who will upgrade their code base to the latest version and call the SP version with the out parameter. If they only call the first signature (without the out parameter) they will have no code changes.

[sjh37]

The above commit works for EF Core 7. I'll add support to add EF 6, EF Core 2 - 6.

[sjh37]

Ran into a few issues during testing, such as

CREATE PROCEDURE dbo.ThisHasMixedOutParameters
    @Foo DATETIME NULL,
    @FirstOutParam INT OUT,
    @Bar DATETIME NULL,
    @SecondOutParam INT OUT,
    @Baz DATETIME NULL
AS
BEGIN
    INSERT INTO TableA (TableADesc) VALUES ('Test');

    SET @FirstOutParam = @@IDENTITY;
    SET @SecondOutParam = SCOPE_IDENTITY();
END;

Which created

public int ThisHasMixedOutParameters(
    DateTime? foo = null,
    out int? firstOutParam,
    DateTime? bar = null,
    out int? secondOutParam,
    DateTime? baz = null)
{ ... }

The best solution is to make only the last parameters nullable after any out or non-nullable parameters.

[sjh37]

This has now been completed and will be in the next release

[sjh37]

Released in v3.8.1