CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
Adam Ness
Adam Ness

Reputation: 6273

Creating a generalized type for use as a table value parameter

I'm finding that there are several points in the stored procedures that I'm creating where I want a stored procedure to accept a list of IDs in various tables that the SP should perform an operation on. I'm considering using Table Value Parameters. What I'm wondering about is whether it's a good practice to create a single Type which just contains a single "ID int" column, and use that as the data type for all of my TVP's, or whether it's a better practice to define a type for each SP, in case I end up in the future wanting to expand the values passed.

Does anyone have experience with this?

edit In case it makes any difference to your recommendation, the ID lists that I'm passing may be on the order of 150,000 entries.

Upvotes: 1

Views: 1440

Answers (2)

billinkc
billinkc

Reputation: 61211

What I can offer you in the way of experience is that if you find you need to change the definition of a user defined table type, you will need to drop every reference to the type before you can diddle with it. Down in the Cons section mentioned that great annoyance. So for us, it was worth the code maintenance to define unique table types based on expected usage, even if it matched existing types. YMMV

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Why not just use an inline table-valued function that splits the list and returns a table? If you need to use it multiple times you can just call it once and stuff the results into a local table variable. To me TVP is most beneficial when you need to get complex arrays from client applications into the database - I'm not sure if it's worth the trouble for a single column containing IDs.

Upvotes: 1

Related Questions