Multiple rows Sql Server update using a Dedicated Table Value parameter - Stored Procedure

Question

Using the below stored procedure I am able to select a variable section of a table, given the Row index of each record. I tried to create another stored procedure similar to the MulTselect, only this time the table value parameter will have another column for the new value for a MulTUpdate stored procedure.

[RowIndex] int, [NewVal] [varchar] (4000) NOT NULL

the question is, given the record - Rowindex and "NewValue", is it possible to do an UPDATE on multiple records like in select procedure below?

--sproc signature

CREATE Proc [dbo].[MulTSELECTViaRowIndexSpTVP] 
@SelectedTableName varchar (50),
@SelectedAction varchar(10),
@TestMulTSELECTViaRowIndexTVPar dbo.TestMulTSELECTViaRowIndexTVType READONLY


SET @CmdStr = 'SELECT * FROM ' + @SelectedTableName + ' 
               WHERE RowIndex in (SELECT RowIndex from @TestMulTSELECTViaRowIndexTVPar);'

EXEC sp_executesql @CmdStr,N' @TestMulTSELECTViaRowIndexTVPar dbo.TestMulTSELECTViaRowIndexTVType READONLY',
         @TestMulTSELECTViaRowIndexTVPar= @TestMulTSELECTViaRowIndexTVPar

Answer 1

AS I was looking through my old stored procedures i found one that stared me thinking in the right way, so this is what i got to compile, not yet tested.

Declare @TotalRec int = (Select COUNT(RowIndex) from @TestMulTiActMulTColIndexTVPar);
Declare @Iter int =0, @Count int =0;
While @Iter < @TotalRec
     Begin
        Declare @curRowIndex int=
                (
                    select RowIndex from
                        (select row_number() over (order by RowIndex) as RowN,
                        * from  @TestMulTiActMulTColIndexTVPar) T where T.RowN =(@Iter+1)
               );
        Declare @CurVal varchar(4000) = (Select [StrVal] from @TestMulTiActMulTColIndexTVPar WHERE RowIndex=@curRowIndex);
        SET @CmdStr = 'UPDATE ' + @SelectedSDTOName + ' SET ' + @SelectedColName + ' = ' + QUOTENAME(@CurVal, '''') + ' WHERE RowIndex = ' + CAST(@curRowIndex as nvarchar);

        EXEC sp_executesql @CmdStr,N' @TestMulTiActMulTColIndexTVPar dbo.TestMulTiActMulTColIndexTVType READONLY',
                                 @TestMulTiActMulTColIndexTVPar= @TestMulTiActMulTColIndexTVPar;
        Set @Iter = @Iter + 1;
    End

SELECT 'EffRows' = @Iter; 

this is the best i could compose with my skills, as it iterates it's executing each update command(within the while loop) separately as opposed to the select procedure

SELECT * FROM ' + @SelectedTableName + ' 
           WHERE RowIndex in (SELECT RowIndex from @TestMulTSELECTViaRowIndexTVPar);'

so i don't know if it will best perform as i coded here... any comment will be very welcome.

and thanks for your time.