http://www.sqlservercentral.com/blogs/discussionofsqlserver/2013/02/20/a-month-of-powershell-day-20-databases-table-valued-functions/

Printed 2014/04/19 07:58AM

A Month of PowerShell – Day 20 (Databases: Table-Valued Functions)

By Wayne Sheffield, 2013/02/20

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:

Source code   
#Assign variables
$Instance   = "localhost\SQL2012"
$DBName     = "PoSh"
$SchemaName = "Common"
$ObjectName = "GetDateOnlyITVF"
 
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
 
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
 
# assign the database name to a variable
$MyDB = $Server.Databases[$DBName]
 
# assign the schema to a variable
$Schema = $MyDB.Schemas[$SchemaName]
 
# assign the function to a variable if it exists
$Object = $MyDB.UserDefinedFunctions.Item($ObjectName, $SchemaName)
 
IF (!($Object)) #Check to see if the function exists
{
    # it doesn't exist, so create it
    $Object = New-Object ("$SMO.UserDefinedFunction") ($MyDB, $ObjectName, $SchemaName)
    $Object.TextMode = $true
    $Object.TextHeader = "CREATE FUNCTION [$SchemaName].[$ObjectName] (@Date DATETIME)
RETURNS TABLE
AS"
    $Object.TextBody = "RETURN SELECT ConvertedDate = DATEADD(DAY, DATEDIFF(DAY, '1900-01-01T00:00:00', @Date), '1900-01-01T00:00:00');"
    $Object.Create()
}

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

Source code   
IF (!($Object)) #Check to see if the function exists
{
    # it doesn't exist, so create it
    $Object = New-Object ("$SMO.UserDefinedFunction") ($MyDB, $ObjectName, $SchemaName)
    $Object.TextMode = $false
    $Param = New-Object ("$SMO.UserDefinedFunctionParameter") ($Object, '@Date', [Microsoft.SqlServer.Management.Smo.DataType]::DateTime)
    $Object.Parameters.Add($Param)
    $Object.FunctionType = [Microsoft.SqlServer.Management.Smo.UserDefinedFunctionType]::Inline
    $Object.TextBody = "RETURN SELECT ConvertedDate = DATEADD(DAY, DATEDIFF(DAY, '1900-01-01T00:00:00', @Date), '1900-01-01T00:00:00');"
    $Object.Create()
}

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:

Source code   
#Assign variables
$Instance   = "localhost\SQL2012"
$DBName     = "PoSh"
$SchemaName = "Common"
$ObjectName = "GetDateOnlyMSTVF"
 
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
 
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
 
# assign the database name to a variable
$MyDB = $Server.Databases[$DBName]
 
# assign the schema to a variable
$Schema = $MyDB.Schemas[$SchemaName]
 
# assign the function to a variable if it exists
$Object = $MyDB.UserDefinedFunctions.Item($ObjectName, $SchemaName)
 
IF (!($Object)) #Check to see if the function exists
{
    # it doesn't exist, so create it
    $Object = New-Object ("$SMO.UserDefinedFunction") ($MyDB, $ObjectName, $SchemaName)
    $Object.TextMode = $true
    $Object.TextHeader = "CREATE FUNCTION [$SchemaName].[$ObjectName] (@Date DATETIME)
RETURNS @Results TABLE (ConvertedDate DATETIME)
AS"
    $Object.TextBody = @'
BEGIN
    INSERT INTO @Results
        SELECT DATEADD(DAY, DATEDIFF(DAY, '1900-01-01T00:00:00', @Date), '1900-01-01T00:00:00');
    RETURN;
END
'@
    $Object.Create()
}

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.

Source code   
IF (!($Object)) #Check to see if the function exists
{
    # it doesn't exist, so create it
    $Object = New-Object ("$SMO.UserDefinedFunction") ($MyDB, $ObjectName, $SchemaName)
    $Object.TextMode = $false
    # Create a new parameter and add it to the function
    $Param = New-Object ("$SMO.UserDefinedFunctionParameter") ($Object, '@Date', [Microsoft.SqlServer.Management.Smo.DataType]::DateTime)
    $Object.Parameters.Add($Param)
    # Specify the function type and table variable name
    $Object.FunctionType = [Microsoft.SqlServer.Management.Smo.UserDefinedFunctionType]::Table
    $Object.TableVariableName = "@Results"
    # Create and add the table variable columns to the function
    $Column = New-Object ("$SMO.Column") ($Object, 'ConvertedDate', [Microsoft.SqlServer.Management.Smo.DataType]::DateTime)
    $Object.Columns.Add($Column)
    $Object.TextBody = @'
BEGIN
    INSERT INTO @Results
    SELECT ConvertedDate = DATEADD(DAY, DATEDIFF(DAY, '1900-01-01T00:00:00', @Date), '1900-01-01T00:00:00');
    RETURN;
END
'@
    $Object.Create()
}

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.