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

Question

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

something like....

CREATE PROCEDURE v.x

@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

AS

BEGIN

--- SQL queries goes here

END

RETURN

---

Developer Frog Haven Enterprises

Answer 1

Hi Elbilo.

Try this approach:

@parm2 udt_1D_intArray NULL

...

---

karepa

Answer 2

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

Answer 3

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?

Thanks.

---

Developer Frog Haven Enterprises

Answer 4

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

Answer 5

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

http://msdn.microsoft.com/en-us/library/bb510489.aspx

---

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/