Welcome to Day 20 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
#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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
#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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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.