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?
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.