In my last blog, I wrote about how to create a PowerShell module. Now, it's time to learn how to write a basic PowerShell function to put in that module. We are going to keep the functionality of this function very simple, and just write to return the version of the SQL Server passed into the function. To begin, each function starts with a verb and is supposed to come from an approved list of verbs.  We are going to call ours Get-SQLVersion. Next, the whole function is between parentheses { }.

function Get-SQLVersion {

}

At the top, we are going to want to insert comments that will act as our help.  For each . section, you should fill-in the information about your function that can be returned for Get-Help to help remind yourself or others how to use your function.

function Get-SQLVersion {
<#
.SYNOPSIS
Return SQL Version for a server
.DESCRIPTION
Run Invoke-SQLCmd against server provide and return results of SELECT @@VERSION back in a grid
.EXAMPLE
Get-SQLVersion -ServerName "ServerName"
.NOTES
Author Tracy Boggiano, written January 7, 2018eac
#>
}
Next, we need to discuss adding parameters to the function. In this function, we will only have one parameter ServerName.  Each parameter is defined by its type and I like to specify at a minimal rather it is required parameter or not. Different parameter types can be looked up here. We will be using the string type.
function Get-SQLVersion {
<#
.SYNOPSIS
Return SQL Version for a server
.DESCRIPTION
Run Invoke-SQLCmd against server provide and return results of SELECT @@VERSION back in a grid
.EXAMPLE
Get-SQLVersion -ServerName "ServerName"
.NOTES
Author Tracy Boggiano, written January 7, 2018
#>
Param(
[Parameter(Mandatory = $true)]
[string]$ServerName
)
}

Next, we are going to add the actual code to call the command to the server we specified, and return the results. We will wrap our call to Invoke-SQLCmd inside of TRY..CATCH block to catch any errors and write them out to screen.

function Get-SQLVersion {
<#
.SYNOPSIS
Return SQL Version for a server
.DESCRIPTION
Run Invoke-SQLCmd against server provide and return results of SELECT @@VERSION back in a grid
.EXAMPLE
Get-SQLVersion -ServerName "ServerName"
.NOTES
Author Tracy Boggiano, written January 7, 2018
#>
Param(
[Parameter(Mandatory = $true)]
[string]$ServerName
)
try {
Invoke-Sqlcmd -ServerInstance $ServerName -Database master -Query "SELECT @@SERVERNAME, @@VERSION" -QueryTimeout 60 -ErrorAction Stop | Out-GridView
}
catch {
Write-Host "failed: $($_.Exception.Message)" -ForegroundColor Red
throw
}
}

So now, you can call the function, and save it into your functions folder of the module you created from our first blog. Then we can call it from PowerShell by importing the module, then calling the function.

Import-Module DBAFunctions

Get-SQLVersion -ServerName "ServerName"

Grid Output

Anonymous