You can create a multi-statement table-valued function (MSTVF) in SQL Server using the T-SQL CREATE FUNCTION syntax.
Tag: table-valued functions
Create an Inline Table-Valued Function (ITVF) in SQL Server
You can create an inline table-valued function (ITVF) in SQL Server using the T-SQL CREATE FUNCTION syntax.
Delete Data via a Table-Valued Function in SQL Server
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.
Update Data via a Table-Valued Function in SQL Server
In SQL Server, it’s possible to update data via a table-valued function.
What I mean is, you can update data in the underlying tables that the function queries.
For example, if your function returns someone’s first name from a table, you can update their first name by running an UPDATE statement against the function instead of the table.
Note that this only works on inline table-valued functions (ITVFs). As far as I’m aware, it won’t work on multi-statement table-valued functions (MSTVFs).
Also, the columns you update will need to be valid columns in the function’s query.
Insert Data via a Table-Valued Function in SQL Server
In SQL Server, it’s possible to insert data via a table-valued function (TVF).
By this, I mean insert rows in the underlying tables that the function queries.
To insert a new row via a TVF, simply use the same T-SQL INSERT syntax that you’d use if inserting data directly into the table.
Select Data via a Table-Valued Function in SQL Server
The SELECT statement is probably the most commonly used statement in SQL Server. Most of the time this statement is run against a view or directly against a table to retrieve rows of tabular data.
But views and tables aren’t the only objects you can run a SELECT statement on. The SELECT statement can also be used on other objects such as rowset functions, OPENXML, and user-defined functions.
This article provides an example of selecting data via a table-valued function.
What is a Table-Valued Function in SQL Server?
In SQL Server, a table-valued function (TVF) is a user-defined function that returns a table. This is in contrast to a scalar function, which returns a single value.
You can invoke a table-valued function in the same way that you can query a table. For example, you can use it in a SELECT statement. In some cases, table-valued functions can also be used to update, delete, and insert data.
Find Out if an Object is a Table-Valued Function in SQL Server with OBJECTPROPERTY()
You can use the OBJECTPROPERTY() function in SQL Server to check whether an object is a table-valued function or not.
To do this, pass the object ID as the first argument, and IsTableFunction as the second argument. The function returns a 1 or a 0 depending on whether or not it’s a table-valued function.
A return value of 1 means that it is a table-valued function, and a value of 0 means that it’s not.
Find the Columns Returned by a Table-Valued Function (T-SQL Examples)
In SQL Server, you can use the Transact-SQL ROUTINE_COLUMNS system information schema view to find the columns returned by a table-valued function.
More specifically, it returns one row for each column returned by the table-valued functions that can be accessed by the current user in the current database.
To use this view, specify the fully qualified name of INFORMATION_SCHEMA.ROUTINE_COLUMNS.