Passing empty DataTable to SQL Server stored procedure through database context

Question

I am trying to pass empty DataTable to a SQL Server stored procedure but I am getting error executing it. The error is:

There are not enough fields in the Structured type. Structured types must have at least one field.

If I pass null for this then I get a different error:

When executing a command, parameters must be exclusively database parameters or values

This code works when I am passing non empty List.

Below is the pseudo code of what I am doing. This stored procedure takes 6 parameters and @rt_AppList is one of the input parameter (of a custom type)

C# code:

var appList = new SqlParameter("@rt_AppList", SqlDbType.Structured);
appList.TypeName = "[brs].StringListType";
appList.Value = GetTypes(alertData.approverIDs);

var resp = context.Database.SqlQuery<<IEnumerable<string>>
                    (Connection, @"EXEC [brs].[GEN_ALERTS] @rt_AppList", appList);


private DataTable GetTypes(List<string> typeList)
{
    var typeTable = new DataTable();

    if (typeList != null && typeList.Count > 0)
    {
        typeTable.Columns.Add("Type", typeof(string));

        for (int i = 0; i < typeList.Count; i++)
        {
            typeTable.Rows.Add(typeList[i]);
        }
    }

    return typeTable;
}

Stored procedure:

CREATE PROCEDURE [brs].[GEN_ALERTS]
    @rt_AppList [brs].StringListType READONLY,

Table type used:

CREATE TYPE [brs].[StringListType] 
    AS TABLE ([Type] [nvarchar](max) NULL)

-- Edit I also tried setting

appList.Value = DBNull.Value;

But now I am getting a different error.

DBNull value for parameter '@rt_AppList' is not supported. Table-valued parameters cannot be DBNull.

Has anyone faced this issue? Appreciate your inputs about this.

Regards, Sajesh

Answer 1

I am able to fix this, the error message was misleading, issue was with the other parameters not with the table value parameter. I don't need to set to DBNull as it is not allowed for TVP. My previous code is working correctly.