Null, Empty or default value for Table Type input of a SQL Function

Question

I have a custom table type calls UniqueIdentifierTable

CREATE TYPE [dbo].[UniqueidentifierTable] AS TABLE(
   [Value] [UNIQUEIDENTIFIER] NULL
)

I have a function that take in this table type as an input:

CREATE FUNCTION [dbo].[MyFunction](@Input UniqueidentifierTable READONLY) 
RETURN @Output .....

I attempt to call this from my .NET layer by passing in a default or empty value.

.FromSql("SELECT Output FROM MyFunction(NULL)")

The above calls gives me error

'Operand type clash: NULL is incompatible with UniqueidentifierTable'

Upon googling around, some post suggested that leave it empty then it is a default value so I tried to call the function without any input

.FromSql("SELECT Output FROM MyFunction()")

I would get the error

An insufficient number of arguments were supplied for the procedure or function MyFunction.'

How can I pass in a default empty table value type to this SQL function?

Possible duplicate of sql function with two parameters. Call from VB .NET — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jul 23, 2019 at 19:50
Think first. NULL is a scalar value. "Empty" means you are not providing a value for a particular parameter. For the "empty" case, did you provide a default value for your parameter? Nope - then you can't pass "empty" regardless of type. Your function requires a table as a parameter - not a scalar value. One wonders what your function does with an empty table (or a table where any/all rows have a null value) - but that's a different question. So your question boils down to "how to pass a table variable parameter (tvp) to a function". Search on that phrase. — SMor
– SMor, Commented Jul 23, 2019 at 20:03
Possible duplicate of Passing empty list to user defined table type parameter on a scalar function — Dale K
– Dale K, Commented Jul 23, 2019 at 20:06
There is no default empty table value type. T-SQL could have added some mechanism or syntax for that, but it chose not to -- you are required to pass in an empty table of the correct type. — Jeroen Mostert
– Jeroen Mostert, Commented Jul 24, 2019 at 13:47

Fylix · Accepted Answer · 2019-07-24 13:41:28Z

0

Your comments gave me a lot of information to go on, I'd like to post another approach but from the .net standpoint instead of on SQL server.

I manage to create an empty DataTable and pass it in that way as a parameter.

       DataTable table = new DataTable();
       table.Columns.Add("Value", System.Type.GetType("System.Guid"));

       SqlParameter param = new SqlParameter("@Input ", table);
       param.TypeName = "dbo.UniqueidentifierTable";
       param.SqlDbType = SqlDbType.Structured;

       //some more code to call using context... ect..

       .FromSql("SELECT Output FROM MyFunction({0})", param)

edited Jul 24, 2019 at 13:41

answered Jul 23, 2019 at 21:09

Fylix

2,7658 gold badges56 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Null, Empty or default value for Table Type input of a SQL Function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related