T-SQL

Modifying data using table valued functions

Is it possible to modify data in T-SQL using inline table valued functions? It´s surprising, but YES, it´s possible. If we will remember the sentence from BOL, that we can think about inline table valued functions as  they are like parameterized views, then it’s more clear that we can modify data using them like we can do it using views. Let us show how it works.

First we will create dbo.SampleTable with simple data inside:

Modifying-data-with-table-valued-function-1

As next create simple inline table valued function to return all data from dbo.SampleTable:

We will test DML operations firing them against our function now:

INSERT:

Modifying-data-with-table-valued-function-2

UPDATE:

Modifying-data-with-table-valued-function-3

DELETE:

Modifying-data-with-table-valued-function-4

One question can be obvious: How this works with parameters? Pretty well.

We will modify our function to include @ID as a parameter and filter dbo.SampleRows by this value:

And DML operations:

INSERT:

Modifying-data-with-table-valued-function-5

Instead of the NULL parameter value we can use any value but it has no effect on INSERT operation. With UPDATE and DELETE operation there is more fun and @ID value is really useful:

UPDATE:

Modifying-data-with-table-valued-function-6

DELETE:

Modifying-data-with-table-valued-function-7

Conclusion: Yes, it works. BUT: For 99% of people it will be surprise to see this to be used. It is not intuitive enough and I will say slightly against the nature of the SQL standards. Plus we are limited there by lack of implementation of few features we can use with views like WITH CHECK OPTION.

Leave a Reply

Your email address will not be published. Required fields are marked *