Personal Links Minimize

Search Blogs Minimize

Blog Archives Maximize

 OldBlog      
Location: BlogsDataZulu    
Posted by: Eric Wilson 5/4/2009

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 not contain rows. You can still "select *" from it and get the header.

 

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 MS products are internally inconsistent. (And by "love" I mean hate with a passion.)

Permalink |  Trackback
Blog OldBlog Downloads
Copyright 2005-2009 Eric M. Wilson and Reimage LLC Terms Of Use Privacy Statement