Loop through each row of Table valued parameter

Question

Loop through each row of Table valued parameter

narengc

Old Hand

Points: 325
More actions
June 16, 2015 at 6:34 am

#318963

I need some help on looping through each row of Table valued parameter.
I have user defined type
CREATE TYPE [dbo].[PCS_SPEC_ATTR_VALUE] AS TABLE(
[ATTR_NAME] [varchar](256) NULL,
[ATTR_VAL] [varchar](4000) NULL
)
I am using this type in my procedure like
@P_TYPE VARCHAR(4000),
@P_SCOPE VARCHAR(4000),
@P_PART_CLS_ATTR PCS_SPEC_ATTR_VALUE readonly
I am using P_PART_CLS_ATTR as input where I can insert the data as attr_name and attr_value. I want to access each row of inserted values like row by row. I need take attribute name and value process them and return for inserting..can any one help how to access the values in row by row?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

narengc (6/16/2015)
I need some help on looping through each row of Table valued parameter.
I have user defined type
CREATE TYPE [dbo].[PCS_SPEC_ATTR_VALUE] AS TABLE(
[ATTR_NAME] [varchar](256) NULL,
[ATTR_VAL] [varchar](4000) NULL
)
I am using this type in my procedure like
@P_TYPE VARCHAR(4000),
@P_SCOPE VARCHAR(4000),
@P_PART_CLS_ATTR PCS_SPEC_ATTR_VALUE readonly
I am using P_PART_CLS_ATTR as input where I can insert the data as attr_name and attr_value. I want to access each row of inserted values like row by row. I need take attribute name and value process them and return for inserting..can any one help how to access the values in row by row?

Superficially (because your details are somewhat superficial), it looks like you are overengineering your solution. Can you provide more detail? For instance, how will you populate the table variable @P_PART_CLS_ATTR?

What exactly do you mean by "process them"?

Why won't an UPDATE suffice?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

I want to access each row of inserted values like row by row

This will be ringing alarm bells among seasoned users of the site, who will be wondering why you can't process the rows as a set.

Can you explain a bit about what the process is?

narengc Old Hand Points: 325 More actions · Answer 3

Ok, Looks like I have not provided sufficient details to get some help. Just to give you some more back ground I am working on migrating oracle DB to SQL server.

I am trying to convert procedure which basically take user details like Type, scope and some more details like (name, value) in table format. I have to insert these details to Table. Before inserting the records I need to take details inserted by user and check whether they are meeting standards (like length of name is 15 and it is not null) etc... After checking these, i will be taking out user inserted values and insert suitable data types to details given by user and insert them into table. Hope this will give an idea on what I am doing and getting the help I am looking for.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

narengc (6/16/2015)
Ok, Looks like I have not provided sufficient details to get some help. Just to give you some more back ground I am working on migrating oracle DB to SQL server.
I am trying to convert procedure which basically take user details like Type, scope and some more details like (name, value) in table format.

Where is this table in relation to your SQL Server?

[Quote]I have to insert these details to Table. [/quote]

Where?

Before inserting the records I need to take details inserted by user and check whether they are meeting standards

Is this the same table as you are importing above or a different table?

(like length of name is 15 and it is not null) etc... After checking these, i will be taking out user inserted values

are there any values which are not "user inserted"?

and insert suitable data types to details given by user

another table or the same table?

and insert them into table. Hope this will give an idea on what I am doing and getting the help I am looking for.

You will probably expect a full and detailed flow diagram with column names and data types, and scope in relation to both servers - and it's certainly possible to do. But not with the details you have supplied. Far too vague.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden