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

Question

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

It is the SQL Azure server 12.0.2000.8...

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

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?

@Charlieface: I can or try to create simplified function that would behave the same, or I can show here some simplification (basically notes). But I cannot reveal full code. I will try the to prepare the "fully working" example this evening. — pepr
– pepr, Commented Jun 25 at 13:34
To be honest this sounds like a problem from a design point. You have table variables in a scalar function. Maybe instead of deleting from your table variable you can adjust the insert so it doesn't insert the rows you later want to remove? — Sean Lange
– Sean Lange, Commented Jun 25 at 13:43
Give us a minimal reproducible example. If your FUNCTION is a single statement then that implies it's an inline table value function; you can't use DDL statements for a RETURN. Therefore you must be using a multi-line TVF, which are awful for performance and best avoided. It does smell like an XY Problem to me. — Thom A
– Thom A ♦, Commented Jun 25 at 13:51
If both tables are defined in the function, then it's a false positive. SSMS version is more relevant than Azure version here... — siggemannen
– siggemannen, Commented Jun 25 at 14:06

Dale K · Accepted Answer · 2025-06-25 18:52:24Z

7

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.

edited Jun 25 at 18:52

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Jun 25 at 14:14

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DavidG Jun 25 at 14:17

Interestingly, if you just use DELETE FROM @v then it parses fine. I guess it's the alias that is the problem.

Charlieface Jun 25 at 14:18

Yes I said only if a table alias is used

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related