Nullable Table Valued Parameter in Stored Procedure

Question

I have this procedure

CREATE PROCEDURE dbo.spProcedure1
    @intArray as dbo.intArray READONLY
AS
BEGIN
-- ...
END

which use user type as a parameter

CREATE TYPE dbo.IntArray AS TABLE (IntValue int NULL)

and I am calling the procedure from the C# ASP.NET MVC 4 project

    // creating empty SQL @IntArray parameter

        var emptyIntDataTable = new DataTable();
        emptyIntDataTable.Columns.Add("IntValue");

    // calling stored procedure

        return Database.SqlQuery<int>(
            @"spProcedure1 @p1",
            new SqlParameter("p1", (object)Utils.ToDataTable(m.IntArray) ?? emptyIntDataTable)
        ).ToList();

    // ToDataTable method which is returning null

        public static DataTable ToDataTable<T>(this IList<T> data)
                {
                    if (data == null)
                        return null;

                    ... // code omitted because it is not working yet                
}

the error which is throwed when calling stored procedure is

The table type parameter 'p1' must have a valid type name.

How to pass an empty table value?

Passing the list instead of datatable throw following error

var emptyIntDataTable = new List<int>;

No mapping exists from object type System.Collections.Generic.List`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] to a known managed provider native type.

an empty list? , which in the code would create an empty datatable, but in the next method, which constructs the parameter list to feed to the stored proc, if the datatable is empty, simply omit that parameter. — Charles Bretana
The Table Valued parameter cannot be optional therefore I have to pass an empty DataTable which is unfortunately not working, can you please provide some code example ? I will try to pass List<int> instead of DataTable and will let you know — Muflix
Try it, I know it says it is not optional, but if you call the Stored proc without it, it accepts it and it works just fine. In SQL Enterprise manager, it even shows in red (indicating an error), but if you hit execute it works. — Charles Bretana
Oh now I see your point, but if the stored procedure has the parameter specified in the first parameter of SqlQuery, omitting the parameter will throw an error that the @p1 is not specified. I also tried the List<int> and i puts an error to the question. — Muflix
Are you not familiar with an if statement? C# does have this capability. Wherever that line of code is, put TWO of them one for when the list has stuff in it (as it is) and one WITHOUT THE @p1 parameter. — Charles Bretana

Charles Bretana Charles Bretana · Accepted Answer · 2016-08-31T13:10:18

In your code:

where it says:

return Database.SqlQuery<int>(
    @"spProcedure1 @p1", new SqlParameter("p1", 
        (object)Utils.ToDataTable(m.IntArray) ?? emptyIntDataTable)
).ToList();

Change it to read:

 return m.IntArray.Length > 0? 
        Database.SqlQuery<int>(@"spProcedure1 @p1",
            new SqlParameter("p1", 
               (object)Utils.ToDataTable(m.IntArray))).ToList():
        Database.SqlQuery<int>(@"spProcedure1")).ToList();

sample to show how to not pass table parameter

CREATE TYPE dbo.KeyIds]
AS TABLE(pkId int NOT NULL,
PRIMARY KEY CLUSTERED (pkId ASC)
WITH (IGNORE_DUP_KEY = OFF))
Go
-- ------------------------------
Create procedure testProc 
    @aIds dbo.keyIds readonly
as 
Set NoCount On
    if exists (select * from @aIds) 
        Select * from @aIds
    else
        Select 'No Aids passed in'
Go
-- ------------------------------

Exec dbo.testProc -- <--- Here I am NOT passing the @aids parameter

But, even though I am NOT passing the @aids parameter it still works, and the subquery (select * from @aIds) still functions, and since it is an empty datatable the SP returns the empty message 'No Aids passed in'.

On the other hand, if you pass the parameter

Declare @MyIds dbo.keyIds
Insert @MyIds Values(1)
Insert @MyIds Values(2)
Insert @MyIds Values(3)
Insert @MyIds Values(4)
Insert @MyIds Values(5)
Exec dbo.testProc @MyIds -- <--- Here I AM passing the @aids parameter

it outputs the contents of the datatable parameter

C# code example...

 public DataTable GetAccountTransactions(IEnumerable<int> accountIds)
    {
        const string procName = "FetchAccountTransactionData";

        var acctIds = accountIds == null ? 
              new List<int>() : accountIds.ToList();
        // -------------------------------------------------
        var parms = DbParamList.Make(); 
            // DbParamList is a List<IDbDataParameter>
        // See here, ONLY ADD PARAMETER if list is NOT empty!
        if (acctIds.Count > 0) 
            parms.AddSQLTableParm("aIds", acctIds);

        try
        { // following constructs command obkect and calls SP
            return Utilities.GetDataTable(schemaNm + "." + 
                            procName, parms, copConn);
        }
        catch (SqlException dbX)
        { 
           // Exception stuff
        }
    }


    public class DbParamSortedList : SortedList<string,IDbDataParameter> { }

Nullable Table Valued Parameter in Stored Procedure

2 Answers