http://www.sqlservercentral.com/blogs/discussionofsqlserver/2013/02/19/a-month-of-powershell-day-19-databases-scalar-functions/

Printed 2014/07/29 07:32PM

A Month of PowerShell – Day 19 (Databases: Scalar Functions)

By Wayne Sheffield, 2013/02/19

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.

Source code   
#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.

Source code   
#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.

Source code   
#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()
}

 


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