loop through each row in table value parameter

Question

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?

Answer 1

Below is an example code snippet of the cursor fetch. The comment is where you can insert your "process them and return for inserting" code.

DECLARE
      @ATTR_NAME varchar(256)
    , @ATTR_VAL varchar(4000);

DECLARE NameAndValue CURSOR LOCAL FAST_FORWARD FOR
    SELECT 
          ATTR_NAME
        , ATTR_VAL
    FROM @P_PART_CLS_ATTR;

OPEN NameAndValue;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM NameAndValue INTO @ATTR_NAME, @ATTR_VALUE;
    IF @@FETCH_STATUS = -1 BREAK;
    --process returned @ATTR_NAME and @ATTR_VALUE values here
END;
CLOSE NameAndValue;
DEALLOCATE NameAndValue;