PowerShell for the DBA - Basic Functions

By: Alejandro Cobar   |   Updated: 2022-05-05   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > PowerShell

Problem

Moving forward with the series, I would like to shift attention to functions in PowerShell. Functions can help us encapsulate very specific logic in our applications and re-use that logic in many different places, keeping our code cleaner, more compact and modular.

Solution

In this tutorial, we will learn how to declare and use functions in PowerShell, covering several examples to get a good understanding of their definition and usage.

Initial considerations

• A very basic knowledge of coding in any other programming language will be assumed.
• No interaction with Microsoft SQL Server will be covered in this article, that will be covered in a separate article.

Important note: I will be presenting all the examples using Windows PowerShell, but that shouldn't be a showstopper for you to follow along in any other OS of your choosing.

Functions in General

If you ever find yourself writing blocks of code that are being used all over a script, or in several scripts, then such blocks of code are perfect candidates to be wrapped inside a function. Usually, a function receives a parameter (or a set of parameters) and returns a value (sometimes not, but that varies within each programming language).

PowerShell Functions

In PowerShell, a function may optionally receive one or more inputs as a parameter, and may optionally return a value. There are two kinds of function in PowerShell, basic and advanced. However, this article will only cover basic functions and advanced will be covered in a future article, along with cmdlets.

Let's take a look at a few simple function examples of how can you define a basic function.  Here is the syntax for a function that takes no input and doesn't return a value:

function myFirstFunction{
   Write-Host "I have very little purpose in the scripting world…"
}

Here's a running example of it:

Yes, it is as simple as throwing the name of the function in the script and it will be executed.

Here is a function that receives an input and just writes the result:

function myFirstFunction{
    param( 
       [Int]$p1, 
       [Int]$p2 
    ) 
  
    Write-Host $p1+$p2 
}

Here it is in action:

Here is another function that receives an input and returns an output variable:

function myFirstFunction{
    param( 
       [Int]$p1, 
       [Int]$p2 
    )
 
    return $p1+$p2 
}

Here it is in action where we then display the output variable:

The main difference, versus the one that doesn't return a value, is that you can assign the function to a variable and use the output in a different section of your script (nothing different than what you see in other programming languages).

What if I want to make a specific parameter mandatory?

function myFirstFunction{
    param( 
       [Int]$p1, 
       [parameter(mandatory)] 
       [Int]$p2 
    )
 
    return $p1+$p2 
}

In this example, I'm specifying that parameter $p2 is mandatory.

When I invoke the function and only pass a value for $p1, then PowerShell will ask me to provide a value for $p2:

Now, what happens if I want to pass a value only for $p2, but not for $p1?

For this you use something called named parameters while passing the values to the function, because if you don't specify the name of the parameter that you are referencing, then PowerShell will use the ordinal position of each to know what value it will pass respectively.

As you can see, I'm specifying a value for parameter $p2 and not for $p1, and this time PowerShell won't give me a message because $p1 is an optional parameter.

Best Practices for Functions

Here are some general and specific best practices while working with functions in PowerShell:

Name your function as descriptive as possible

To make this easy to understand for future changes or other developers, name your functions using a method that easily gives you an idea of what the function is used for.

Include a comment section

Include comments either within the function or right before defining it, to describe its purpose so that others can understand why you created it. Below shows how to include comments in PowerShell.

Single line comment example

#This is a comment

Multi-line comment example

<# 
This is a comment 
with 
multiple lines 
#> 

Follow a standard for parameters

If you will be working with parameters, then it is a very good idea to follow a standard that allows you (or anybody) to have an homogeneous structure in the definition that makes the code readable. Let me expand on this with an example:

The parameter block lets you specify a positional value, which will map to the order that you pass the unnamed parameter values while invoking the function. If you don't specify it, then it will follow the natural order in which you have laid them out; it is not a mandatory thing, but the option is there if you need to use it.

In this example, I'm explicitly saying that $p1 is an optional parameter and the other two are mandatory. So as you can see, even if you can omit certain parts of the definition because PowerShell will figure them out by default, it gives your code an homogeneous structure that is very digestible to the reader's eye.

Now, what if I not only want a specific parameter as mandatory, but I also want to allow only certain values for it?

You can use the "ValidateSet" option and specify the list of valid values for that parameter.

As you can see, PowerShell will tell me there's something wrong because I've entered an invalid value for parameter $p1. Let's see what it does if I enter a value within the specified range:

Yep, worked as expected. So this is just PowerShell giving us additional options to craft our functions with customizations that can be very handy while trying to tackle a problem.

Calling Functions from Another Script

If functions are blocks of code that can be re-used as often as we like, how inconvenient it would be if we didn't have a way to invoke functions from other PowerShell scripts right? Let's take a look at how this can be achieved.

So I have created a new script file called test.ps1 and in the first line I've put a dot and the path where my function is hosted. After that, I'm able to invoke it without any issues.

Using Functions without a Script File

Yes, PowerShell allows you to define a function directly in the PowerShell console and use it right away, without having the need to craft a script file and execute it in the console. Let me show you how this works.

So I have opened a fresh new empty PowerShell console and I have copy/pasted the exact same code of the function that I have used in the latest screenshots, and then I simply hit enter. After that, I'm able to call the function right there without having to do anything else.

Ease up the consumption of functions with many parameters

This is just a bonus trick that you can use to make your life easier when dealing with functions that have a lot of parameters. Let's take a look at the following example:

function myFirstFunction{
    param(
        [parameter(position = 0,mandatory = $true)]
    [Int]$p1,
        [parameter(position = 1,mandatory = $true)]
    [Int]$p2,
        [parameter(position = 2,mandatory = $true)]
    [Int]$p3,
        [parameter(position = 3,mandatory = $true)]
    [Int]$p4,
        [parameter(position = 4,mandatory = $true)]
    [Int]$p5,
        [parameter(position = 5,mandatory = $true)]
    [Int]$p6,
        [parameter(position = 6,mandatory = $true)]
    [Int]$p7,
        [parameter(position = 7,mandatory = $true)]
    [Int]$p8,
        [parameter(position = 8,mandatory = $true)]
    [Int]$p9,
        [parameter(position = 9,mandatory = $true)]
    [Int]$p10,
        [parameter(position = 10,mandatory = $true)]
    [Int]$p11,
        [parameter(position = 11,mandatory = $true)]
    [Int]$p12
    )
    return $($p1+$p2+$p3+$p4+$p5+$p6+$p7+$p8+$p9+$p10+$p11+$p12)
}
 
$result = myFirstFunction -p1 1 -p2 2 -p3 3 -p4 4 -p5 5 -p6 6 -p7 7 -p8 8 -p9 9 -p10 10 -p11 11 -p12 12
 
Write-Host "The result is:$result" 

As you can see, all 12 parameters are mandatory, so you have to specify a value for all of them. Now, working with the function in the same script isn't that big of a deal, but things start getting annoying when you invoke the function directly in the console and start passing values for the parameters.

See, I didn't even want to finish putting in all 12 parameters… but wait, there's a better way to achieve this.

That's right, if you first create a hash table mapping each parameter with its respective value, and then you invoke the function and pass the hash table as a parameter, then PowerShell will take care of the rest; pretty cool if you ask me!

With this I'm wrapping up basic functions in PowerShell, hoping that you were able to learn something new about PowerShell, as I surely did.

Next Steps

• In case you missed it, check out the previous article PowerShell for the DBA – FOR and WHILE Loops
• Functions can help you write more efficient code, so make sure to use them to your own advantage.
• As usual, try to aim for best practices as much as possible while writing your code. Others and future self will surely appreciate it.
• Always test your functions thoroughly so that you make sure that they work as intended, instead of having to deal with unexpected surprises or weird things in the execution of your script(s).
• In the next article we will be taking a look at advanced functions and cmdlets, so stay tuned!

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips