UPDATE against a table-valued function when declared inline

The other day I mentioned to a friend that you could only perform an UPDATE against a table-valued function in T-SQL when the function was declared as an inline TVF. He basically didn’t understand the point being made at all. That’s not really suprising as the idea of performing an UPDATE against a function does my head in, in terms of everything I ever thought I knew about programming languages.I really don’t think it should even be permitted but regardless, here’s an example:

USE tempdb;

GO

CREATE TABLE dbo.TestTable

( TestTableID int,

  TestTableName varchar(20)

);

GO

INSERT INTO dbo.TestTable

VALUES (1,‘Hello’), (2,‘Greg’);

GO

CREATE FUNCTION dbo.ListGNames()

RETURNS TABLE

AS

RETURN

( SELECT TestTableID, TestTableName

  FROM dbo.TestTable

  WHERE TestTableName LIKE ‘G%’

);

GO

SELECT * FROM dbo.ListGNames();

GO

UPDATE dbo.ListGNames()

SET TestTableName = ‘Fred’

WHERE TestTableName = ‘Greg’;

GO

SELECT * FROM dbo.ListGNames();

GO

SELECT * FROM dbo.TestTable;

GO

Note that the UPDATE is being performed against the *function* not against the underlying table.

What do you think? Should this behavior even be permitted?

2011-05-22