I've been wasting a lot of time trying to figure this out and my frustration has led me here 
I see a lot of articles that start with the topic of this thread and I can't believe how inane they are! Or am I just missing something? Most start off like "SQL Server 2008 introduces the ability to pass a table data type into stored procedures and functions" Is that really possible? All I see is the statements to create the User defined table type and then they proceed to Declare a variable of that type and hardcode in the values???
Here's what I need to accomplish.
Setting up a search routine where the user can pick 1 or more values (there are only 6 total) and I want to return records that have that value(s).
I have the table type setup on server and I have a datatable constructed in vb.net program. I want to pass that table into the stored procedure as a parameter and then the query would end something like
Where Status in (datatable i just sent up). From the examples I've seen I may just have to go the XML route but I will have several more similar queries to write and was hoping someone could provide or point me to an example of how I can actually pass in a datatable as a parameter into a stored procedure.
The first problem I can't get around is defining the parameter. It's not accepting @ParamName definedtabletype.
It says 'parameter or variable has an invalid datatype' ( I tried dbo.definedtabletype and get the same result.
Can what I'm trying actually be done?
TIA
Rob
I've never used it, but my understanding is that an application language can take an Array of elements and transfer that (to SQL) with that array being materialised, from SQL's perspective, as a table valued parameter.
If you can figure out how to "just pass an array as a parameter" then you may well find that it is easy to use / program for.
If the number of criteria is that small then perhaps just pass them as @Param1, @Param2, ..., @Param6 ?
OK, I think I have it now!!
I guess the db had to be refreshed in ssms before it recognized my user defined table type.
The problem with sending vars individually is that there are already 10 other parameters in the sproc so it's getting messy and then I would need to add more code to handle them.
I have my table type defined (ValveStatus only has one column) and I set up a parameter using that type...
@Status ValveStatus READONLY
I'm sending a datatable into the sproc for @Status parameter and my query has 'And z.Status in (Select * from @Status)' in the where clause.
I haven't fully tested but this does seem to work now.
Thanks!
All sounds good 
That probably needs to be either
And z.Status in (Select SomeStatusValueColumnName from @Status)
-- or
And EXISTS (Select * from @Status WHERE SomeStatusValueColumnName = z.Status)