In SQL Server, it’s possible to use a table-valued function (TVF) to delete data from the underlying tables that the TVF queries.

To delete table data via a TVF, simply use the same T-SQL DELETE syntax that you’d use if deleting a row from the table directly.

Example

Here’s a quick example to demonstrate.

The Function

Here’s a quick inline table-valued function that selects basic data from a table.

CREATE FUNCTION dbo.udf_Cats_ITVF()
    RETURNS TABLE
AS
RETURN (
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats
    );
GO

This function selects all rows from a table.

I can also use this function to delete rows from that table.

Select Data with the Function

Here’s how we’d normally invoke the function with a SELECT statement. This enables us to see the current data in the underlying table.

SELECT * FROM udf_Cats_ITVF();

Result:

+---------+-----------+------------+
| CatId   | CatName   | Phone      |
|---------+-----------+------------|
| 1       | Garfield  | 9871237654 |
| 2       | Felix     | 8871237651 |
| 3       | Tom       | 7871237652 |
| 4       | Fetch     | 6871237653 |
| 5       | Scratchy  | 1111111111 |
+---------+-----------+------------+

So there are five rows.

Let’s delete Scratchy.

Delete a Row via the Function

Now let’s use the TVF to delete a row.

DELETE FROM udf_Cats_ITVF()
WHERE CatId = 5;   

SELECT * FROM udf_Cats_ITVF();

Result:

+---------+-----------+------------+
| CatId   | CatName   | Phone      |
|---------+-----------+------------|
| 1       | Garfield  | 9871237654 |
| 2       | Felix     | 8871237651 |
| 3       | Tom       | 7871237652 |
| 4       | Fetch     | 6871237653 |
+---------+-----------+------------+

So I successfully deleted Scratchy using the inline table-valued function.