So it turns out table-valued parameters can be optional with no problem. But it’s non-standard syntax. Normally, you’d have this:
create procedure myProc @Param int = null
The "= null" tells us it’s optional.
With table-valued ones, you have to specify “readonly” in any case (not related to optionality) and they are always also optional ... without specifying "= null". Like this:
create procedure myProc @TblValParam MyTableType readonly
This works and the @TblValParam does not have to be referenced during the call. It seems, conceptually, that the “table value” is always implicitly there, but if not passed in will just contain zero rows. You can still "select *" from it and get the header. Makes sense.
Treating it like a normal parameter, like this:
create procedure myProc @TblValParam MyTableType = null readonly
... gets you this fun error:
Operand type clash: void type is incompatible with MyTableType
Fun.
I love when Microsoft products are internally inconsistent. (And by "love" I mean “hate with a passion.”)
5ad2632c-05a5-4083-b941-577d7bf1512b|1|5.0
MS SQL Server
tsql