using Dynamic SQL - Custom Universal Multi-Action Stored Procedure (with TVPar)

Question

this is the stored procedure i have started to write the problem is i couldn't figure out how to handle the part of reading values from the TV par :

Must declare the table variable "@TestMultiActionViaRowIndexTVPar".

    ALTER Proc [dbo].[MultiActionViaRowIndexSpTVP] 
    @SelectedSDTOName varchar (50), @SelectedAction varchar(10), @TestMultiActionViaRowIndexTVPar dbo.TestMultiActionViaRowIndexTVType READONLY
    as BEGIN

    declare @CmdStr varchar(500) = '';
        if(@SelectedAction = 'SELECT') Begin --test for one of the "Action" types
            SET @CmdStr = 'SELECT * FROM ' + @SelectedSDTOName + 
            ' WHERE RowIndex in (SELECT RowIndex FROM @TestMultiActionViaRowIndexTVPar)'
        End

       --else - other Action - @CmdStr will be according to action...

    --finally execute constructed Cmdstr
    Exec(@CmdStr);
    END

Answer 1

Use sp_executesql to pass a TVP or any other parameter to parameterized dynamic SQL:

ALTER PROC [dbo].[MultiActionViaRowIndexSpTVP] 
      @SelectedSDTOName varchar (50)
    , @SelectedAction varchar(10)
    , @TestMultiActionViaRowIndexTVPar dbo.TestMultiActionViaRowIndexTVType READONLY
AS
DECLARE @CmdStr nvarchar(MAX);

IF @SelectedAction = 'SELECT'
BEGIN
    SET @CmdStr = 'SELECT * FROM ' + @SelectedSDTOName + 
            ' WHERE RowIndex in (SELECT RowIndex FROM @TestMultiActionViaRowIndexTVPar);'
END;

EXEC sp_executesql
     @CmdStr
    ,N'@TestMultiActionViaRowIndexTVPar dbo.TestMultiActionViaRowIndexTVType READONLY'
    ,@TestMultiActionViaRowIndexTVPar = @TestMultiActionViaRowIndexTVPar;
GO