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()
}
