Welcome to Day 20 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

Finishing up the mini-series of objects compiled with T-SQL code, today we’re going to deal with user defined table-valued functions.

Creating an Inline Table-Valued Function

The Department of Redundancy Department wants a table-valued function that performs the same calculation as the fGetDateOnly function that we created yesterday. As it turns out, there isn’t that much to change to create this new function:

[crayon-53f57369d657c052277511/]

The alternate way to create this using PowerShell is to allow the TextHeader property to be calculated by specifying all of the appropriate parameters:

[crayon-53f57369d658f696971516/]

Creating a Multi-Statement Table-Valued Function

To create this as a multi-statement table-valued function is essentially the same if you are specifying the TextHeader property:

[crayon-53f57369d6597405318874/]

The differences come in when you set all of the properties to let the system build the TextHeader property.  In addition to changing the FunctionType property to Table, you also need to set the TableVariableName property, and to add a column to the function’s Columns collection for each of the columns in the table variable.

[crayon-53f57369d659e096856383/]

Altering / Dropping Table-Valued Functions

To alter / drop the functions, there is essentially no difference from yesterday’s session on Scalar function for how to perform the altering.