Update TableType variable in dynamic SQL query

Question

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.

But I got an error:

Msg 10700, Level 16, State 1, Line 1
The table-valued parameter "@ttbl_TagList" is READONLY and cannot be modified.

How can I solve the error?

User defined table type:

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

Procedure:

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

score 0 · Answer 1 · answered Jan 29 '14 at 08:01

TVP can not be updated directly. So try this one -

CREATE PROCEDURE [dbo].[P_ReplaceTemplateTag]   
AS
BEGIN
    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb.dbo.#t1') IS NOT NULL
        DROP TABLE #t1

    DECLARE 
          @ttbl_TagList dbo.tt_TagList
        , @V_Counter TINYINT = 1
        , @V_FinalQuery NVARCHAR(MAX) = ''
        , @V_TagValue NVARCHAR(MAX); 

    INSERT INTO @ttbl_TagList (tagId, tagName, tagValue) 
    SELECT DISTINCT tagId, tagName, ''
    FROM dbo.tbl_Tag;

    WHILE (1 = 1) BEGIN

        SELECT @V_TagValue = 
            CASE 
                WHEN tagId = 1 THEN 'Tag Value 1'
                WHEN tagId = 2 THEN 'Tag Value 2'
                WHEN tagId = 3 THEN 'Tag Value 3'
                WHEN tagId = 4 THEN 'Tag Value 4'
                ELSE ''
            END
        FROM @ttbl_TagList 
        WHERE tagListId = @V_Counter;

        IF (@@ROWCOUNT = 0) BREAK;

        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

        CREATE TABLE #t1
        (
              tagId TINYINT
            , tagName VARCHAR(100)
            , tagValue NVARCHAR(MAX)
        )

        SET @V_FinalQuery = N'
            INSERT INTO #t1
            SELECT tagId, tagName, (CASE tagListId' + @V_FinalQuery + ' END)
            FROM @ttbl_TagList';

        EXEC sys.sp_executesql 
              @V_FinalQuery
            , N'@ttbl_TagList dbo.tt_TagList readonly'
            , @ttbl_TagList;

    END

    SELECT * FROM #t1;

END

Update TableType variable in dynamic SQL query

1 Answers1