Passing empty list to user defined table type parameter on a scalar function

Question

So I have this user-defined table type parameter, which is used in my scalar function and might be empty. I've read this topic about passing empty list to table-valued parameter on a stored procedure: Binding empty list or null value to table valued parameter on a stored procedure (.net)
And basically, as one of the repliers said:

"The trick is: don’t pass in the parameter at all. The default value for a table-valued parameter is an empty table"

However, when I try this on scalar function, I get an error:

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

So how do I pass empty value to table-valued parameter on a scalar function?

Aaron Bertrand · Accepted Answer · 2012-07-18 12:04:17Z

2

Arguments to functions aren't optional, so you need to pass a compatible TVP to the function. I'm not sure I understand the point of a scalar-valued function that takes a TVP type but doesn't need to - what does this function do and how can it do it without the TVP? Are you sure this isn't meant to be a TVF?

Anyway here is how you can pass an empty TVP to a scalar function:

DECLARE @x dbo.TVP_type_name;
SELECT dbo.function_name(@x);

answered Jul 18, 2012 at 12:04

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

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

1 Comment

Think Twice Over a year ago

This scalar-valued function is supposed to return a total search result count. It basically has same parameters as my stored procedure, except my SP returns query result and has two additional parameters for pagination - "page number" and "rows per page". Thanks for help, I see now that I need to declare these TVPs explicitly.

Collectives™ on Stack Overflow

Passing empty list to user defined table type parameter on a scalar function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related