Welcome to Day 19 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.

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

Creating a Scalar Function

Prior to SQL 2008, if you wanted just the date for today, you’d have to do some date manipulation to truncate off the time portion of a datetime value. Let’s make that into a scalar function that can be called as needed.

#Assign variables
$Instance   = "localhost\SQL2012"
$DBName     = "PoSh"
$SchemaName = "Common"
$ObjectName = "fGetDateOnly"
 
#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 = $false
    # specify that this is a scalar function
    $Object.FunctionType = [Microsoft.SqlServer.Management.SMO.UserDefinedFunctionType]::Scalar
    $Object.Datatype = [Microsoft.SqlServer.Management.SMO.DataType]::DateTime
    $Object.TextBody = @'
BEGIN
    RETURN DATEADD(DAY, DATEDIFF(DAY, '1900-01-01T00:00:00', GETDATE()), '1900-01-01T00:00:00');
END
'@
    $Object.Create()
}

Altering Scalar Functions

After working with this function for a bit, you realize that you could use the same functionality for any date. You decide to modify this function to accept a parameter, and the code so that it uses the current date if a null is passed in to the parameter.

#Assign variables
$Instance   = "localhost\SQL2012"
$DBName     = "PoSh"
$SchemaName = "Common"
$ObjectName = "fGetDateOnly"
 
#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 exists, so modify it
    $Object.TextMode = $true
    $Object.TextHeader = "CREATE FUNCTION [$SchemaName].[$ObjectName]
    (@Date DATETIME)
RETURNS DATETIME
AS"
    $Object.TextBody = @'
BEGIN
    RETURN DATEADD(DAY, DATEDIFF(DAY, '1900-01-01T00:00:00', ISNULL(@Date, GETDATE())), '1900-01-01T00:00:00');
END
'@
    $Object.Alter()
}

This example does not consider changes to the Parameters collection if you rename, add or drop parameters for the function.

Dropping a scalar function

To drop the scalar function, just call the function’s drop method.

#Assign variables
$Instance   = "localhost\SQL2012"
$DBName     = "PoSh"
$SchemaName = "Common"
$ObjectName = "fGetDateOnly"
 
#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 exists, so drop it
    $Object.Drop()
}