Is it possible to have a stored procedure with optional user-defined table type parameters? RRS feed

  • Question

  • Is there a way to have a procedure's parameters be optional if the parameter is a user defined table type?

    something like....


    @parm1     varchar(100),

    @parm2     udt_1D_intArray READONLY   --(column name "item") this parameter I'd like to be optional

    @parm3     idt_1D_stringArray READONLY  --(column name "item") this parameter I'd like to be optional



    --- SQL queries goes here



    Developer Frog Haven Enterprises

    Monday, October 22, 2012 11:50 PM


All replies

  • Hi Elbilo.

    Try this approach:

    @parm2 udt_1D_intArray NULL



    Tuesday, October 23, 2012 12:41 AM
  • I believe table valued parameters can not be optional. However, there is a note in community content here http://msdn.microsoft.com/en-us/library/bb510489(SQL.105).aspx which I don't know if this can be trusted or not.

    See also http://datazulu.com/blog/post/table-valued-parameters-and-null.aspx

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    • Edited by Naomi N Tuesday, October 23, 2012 12:21 PM
    • Unmarked as answer by Naomi N Wednesday, November 7, 2012 9:01 PM
    Tuesday, October 23, 2012 1:21 AM
  • It looks like they are right in that if you declare a proc parameter as a user-defined table, with the READONLY tag it is by default an optional parameter. I have not tried all permientations. Once inside the proc if you don't pass a the parameter defined as a EDT you get an empty table.

    I tried to mark your response as the answer but I just get an error message back from IE/MSDN. go figure; huh?


    Developer Frog Haven Enterprises

    Tuesday, October 23, 2012 11:36 PM
  • Using proprietary non-relational "features" like this is a bad design.  Data element names that have the word "array" in them really scare serious SQL programmers. Why don't you show us the code and not the skeleton, so we can fix it? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, November 8, 2012 3:34 AM
  • 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


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Thursday, November 8, 2012 7:35 AM