title: Update TableType variable in dynamic SQL query
body: <p>I had created User defined table type in my database. After that I had declared a variable of that table type in my procedure. And I had return my rest of the logic. At the end I am trying to update that table type variable using dynamic SQL. </p>
  
  <p>But I got an error:</p>
  
  <blockquote>
    <p>Msg 10700, Level 16, State 1, Line 1<br>
    The table-valued parameter "@ttbl_TagList" is READONLY and cannot be modified.</p>
  </blockquote>
  
  <p>How can I solve the error?</p>
  
  <p><strong>User defined table type:</strong></p>
  
  <pre><code>CREATE TYPE [dbo].[tt_TagList] AS TABLE(
      [tagListId] [tinyint] IDENTITY(1,1) NOT NULL,
      [tagId] [tinyint] NOT NULL DEFAULT ((0)),
      [tagName] [varchar](100) NOT NULL DEFAULT (''),
      [tagValue] [nvarchar](max) NOT NULL DEFAULT ('')
  )
  GO
  </code></pre>
  
  <p><strong>Procedure:</strong></p>
  
  <pre><code>CREATE PROCEDURE [dbo].[P_ReplaceTemplateTag]   
  AS
  BEGIN
      SET NOCOUNT ON;
      DECLARE @ttbl_TagList dbo.tt_TagList, @V_TagId INT, @V_Counter TINYINT = 1, 
              @V_FinalQuery NVARCHAR(MAX) = '', @V_TagValue NVARCHAR(MAX); 
  
      INSERT INTO @ttbl_TagList (tagId, tagName, tagValue) 
      SELECT DISTINCT T.tagId, T.tagName, '' AS tagValue 
      FROM dbo.tbl_Tag T;
  
      WHILE (1 = 1)
      BEGIN
          SET @V_TagValue = '';
          SELECT @V_TagId = tagId FROM @ttbl_TagList WHERE tagListId = @V_Counter;
  
          IF (@@ROWCOUNT = 0) 
          BEGIN 
              BREAK;
          END
  
          IF (@V_TagId = 1) 
          BEGIN 
              /* Logic for getting tag value */
              SET @V_TagValue = 'Tag Value 1';
          END
          ELSE IF (@V_TagId = 2) 
          BEGIN 
              /* Logic for getting tag value */
              SET @V_TagValue = 'Tag Value 2';
          END
          ELSE IF (@V_TagId = 3) 
          BEGIN 
              /* Logic for getting tag value */
              SET @V_TagValue = 'Tag Value 3';
          END
          ELSE IF (@V_TagId = 4) 
          BEGIN 
              /* Logic for getting tag value */
              SET @V_TagValue = 'Tag Value 4';
          END
  
          IF (@V_TagValue != '')
          BEGIN 
              SET @V_FinalQuery = @V_FinalQuery + ' WHEN ' + CONVERT(NVARCHAR(10), @V_Counter) + ' THEN ''' + @V_TagValue + '''';
          END
          SET @V_Counter = @V_Counter + 1;
      END
  
      IF (@V_FinalQuery != '')
      BEGIN
          SET @V_FinalQuery = N'UPDATE @ttbl_TagList SET tagValue = (CASE tagListId' + @V_FinalQuery + ' END)';
          EXECUTE sp_executesql @V_FinalQuery, N'@ttbl_TagList dbo.tt_TagList readonly', @ttbl_TagList;
      END
  
      SELECT * FROM @ttbl_TagList;
  END 
  </code></pre>