Updating Multiple Sql Server Rows Using Dedicated Table Value - Stored Procedure

Question

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

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

the question , considering the record - Rowindex and "NewValue", is it possible to UPDATE several records, for example, in the selection 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 looked through my old stored procedures, I found one that looked at me, thinking correctly, so this is what I have 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 through each update command (inside the while loop) separately, unlike the selection procedure

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

so I don’t know if it will be best executed as I have encoded here ... any comments would be very welcome.

and thanks for your time.