Loop through each row in the table value parameter
I need help walking through each row of a table parameter.
I have a user defined type
CREATE TYPE [dbo].[PCS_SPEC_ATTR_VALUE] AS TABLE(
[ATTR_NAME] [varchar](256) NULL,
[ATTR_VAL] [varchar](4000) NULL
)
I use this type in my procedure, for example
@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 data as attr_name and attr_value. I want to access every row of inserted values, for example by row. I need to take the attribute name and value, process them and return them for insertion. Can anyone help how to access the values line by line?
+3
source to share
1 answer
Below is an example of a code snippet to retrieve a cursor. In the comment you can insert your "process them and bring them back for insertion".
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;
+3
source to share