创建通用类型以用作表值参数 [英] Creating a generalized type for use as a table value parameter

问题描述

我发现在我创建的存储过程中有几个点，我希望存储过程接受 SP 应该对其执行操作的各种表中的 ID 列表.我正在考虑使用表值参数.我想知道的是，创建一个只包含一个ID int"列的单一类型并将其用作我所有 TVP 的数据类型是否是一个好习惯，或者是否定义一个更好的实践为每个 SP 键入，以防我将来想要扩展传递的值.

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?

编辑 如果它对您的建议有任何影响，我传递的 ID 列表可能有 150,000 个条目的数量级.

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.

推荐答案

我可以给你的经验是，如果你发现你需要改变一个用户定义的表类型的定义，你将需要删除每次引用该类型之前，您都可以使用它.在缺点部分提到了那个大烦恼.所以对我们来说，根据预期的使用情况定义唯一的表类型是值得的代码维护，即使它匹配现有类型.赞一个

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

这篇关于创建通用类型以用作表值参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！