Welcome to Day 19 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.
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.
|
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 |
#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.
|
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 |
#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.
|
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 |
#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() } |
