how to iterate through table valued parameter type

Question

0.00/5 (No votes)

See more:

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

Posted 9-Dec-15 6:04am

Member 10661997

Add a Solution

Comments

Andy Lanng 9-Dec-15 12:29pm

Don't use it like that. It won't work.

You will need to pass the values as a flat type (i.e. nvarchar csv) and use a tabular function to convert it to a table in the stored procedure. There are plenty of examples of csv to table function on the internet. Just make sure you use it appropriately as it is a huge resource drain for per-row processing

Richard Deeming 9-Dec-15 12:53pm

Table-Valued Parameters[^] will work perfectly well. This is exactly the type of scenario they were made for! :)

Andy Lanng 10-Dec-15 3:58am

Oh rly? I think I need to revisit some code and find out why it didn't work. There goes my weekend :Þ

Rajdeep Debnath 9-Dec-15 15:12pm

Can you provide bit more information....

You can use case statement with select...

Member 10661997 9-Dec-15 19:29pm

we have a phone type, phone number in phone and I need to check if that particular phone type info is already there or not. Also, need to do one more calculation based on phone type.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rajdeep Debnath · Answer 1 · 2015-12-09T21:14:00

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

SQL

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