sql-serverazure-sql-databasessms

"Invalid use of a side-effecting operator 'DELETE' within a function" for the constructed result?


I have code that removes the records in the table variable @small_tab from another table variable @large_tab:

DELETE t1
FROM @large_tab AS t1
JOIN @small_tab AS t2 ON t1.[DBR Addition Cust Disc Group] = t2.[DBR Addition Cust Disc Group]
                      AND t1.[Minimum Quantity] = t2.[Minimum Quantity]
                      AND t1.[Discount _] = t2.[Discount _]
                      AND t1.[Starting Date] = t2.[Starting Date]
                      AND t1.[Ending Date] = t2.[Ending Date]

The code became the part of the user function that constructs and returns the @large_tab. It seems to work fine. However, when displayed in the SSMS, the DELETE t1 is flagged with an error

Invalid use of a side-effecting operator 'DELETE' within a function

marked as a problem

It is the SQL Azure server 12.0.2000.8...

SQL version

... or the property window shows the connected instance of version 12.0.601.

SQL version

I understand that a function should not modify a table as a side effect. However, that one @large_table is the one that is being constructed and is to be returned by the function.

What is the reason for flagging the DELETE command? Is it a false warning? How that should be fixed? Or should I just ignore it?


Solution

  • I can confirm this does appear to be a bug in SSMS, tested in v21.0.1

    It appears whenever a table alias is used in the DELETE statement. It doesn't appear if the same thing is done with an UPDATE or MERGE, or if the non-aliased syntax is used.

    CREATE FUNCTION dbo.f()
    RETURNS INT
    AS BEGIN
        DECLARE @v TABLE (id int PRIMARY KEY);
        DELETE v
        FROM @v v;
    
        RETURN 1;
    END;
    

    Invalid use of a side-effecting operator 'DELETE' within a function.

    The above script is perfectly valid, and will run fine. The error should only be highlighted if a standard or temp table was used, not a table variable.

    I suggest you report this as a bug on the Feedback site.