如何遍历表值参数类型 [英] how to iterate through table valued parameter type

问题描述

我有一个场景，我必须将电话号码列表和其他一些信息传递给存储过程。所以，我使用了用户定义的表类型参数。我需要遍历此表类型参数来进行一些计算。迭代的最佳方法是什么？

I have a scenario where I have to pass list of phone numbers along with some other information to the stored procedure. So, I used user-defined table type parameter. I need to iterate through this table type parameter to do some calculation. What is the best approach to iterate

推荐答案

请尝试下面的内容，如果你的情况如下，请告诉我......

Please try the below and let me know if your scenario is like below...

declare @p1 employee
insert into @p1 values('Raj', 'XYZ', '26747264')
insert into @p1 values('Dev', 'ABC', '12345567')


select * from @p1


declare @p2 phone
insert into @p2 values('XYZ', '033')
insert into @p2 values('DEF', '080')


select * from @p2


update @p1
set a.phoneno = 
(
 case b.phonetype
	 when 'XYZ' then b.code + a.phoneno
	 else a.phoneno
 end
)
from @p1 a
left outer join @p2 b
on a.phonetype = b.phonetype


select 
*
from @p1 a
left outer join @p2 b
on a.phonetype = b.phonetype

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