Today's post is a quick one. This post was inspired by a conversation I had in the #powershellhelp channel on the SQL Community Slack. Splatting is an underused feature of PowerShell. It can make your code easier to read, and provide a lot of flexibility when dealing with things like optional parameters. In this post we'll quickly go over what splatting is, and how it can make your code easier to write and easier to read.

Splatting

I have to start off by saying I hate the name "splatting". I didn't come up with it, and I don't like using it, but it's the only word we have. Splatting is a way to pass parameter values to a function using a single array or hashtable. In this post we'll be talking about hashtables because I think it is the more useful of the two.

Splatting is easy to explain in an example. Let's say you are executing some TSQL code against a SQL Server. More than likely you'll be using Invoke-SqlCmd. Your typical call will look like this:

Invoke-SqlCmd -ServerInstance myserver.markw.dev -Query "SELECT @@VERSION" -Database "master"

That's pretty simple, but lets see how this looks using splatting:

$Params = @{
    ServerInstance = 'myserver.markw.dev'
    Query = "SELECT @@VERSION"
    Database = 'master'
}

Invoke-Sqlcmd @Params

That's it! That's splatting. You can already see how it might make things easier to read, especially if you are calling a function with a lot of parameters. Where splatting really shines though, is optional and conditional parameters.

Optional and conditional parameters with splatting

Let's say we want to use the example above, but this time we want to pass in SQL auth credentials if they are given. Without splatting we'd probably end up with something like this:

if ( $SqlUser -and $SqlPass) {
    Invoke-SqlCmd   -ServerInstance myserver.markw.dev `
                    -Query "SELECT @@VERSION" `
                    -Database "master" `
                    -Username $SqlUser `
                    -Password $SqlPass
} else {
    Invoke-SqlCmd   -ServerInstance myserver.markw.dev `
                    -Query "SELECT @@VERSION" `
                    -Database "master"
}

If you ever want to add a QueryTimeout parameter to this, or change the database, you'll have to alter two function calls. Lets see how this would work with splatting:

$Params = @{
    ServerInstance = 'myserver.markw.dev'
    Query = "SELECT @@VERSION"
    Database = 'master'
}

if ( $SqlUser -and $SqlPass ) {
    $Params.Username = $SqlUser
    $Params.Password = $SqlPass
}

Invoke-SqlCmd @Params

This is a lot cleaner, and if you ever want to add more parameters you can just add them to the $Params hashtable once and be done. One important thing to note here is that when we use splatting, we don't pass the hashtable in using the typical $, we have to use @ so PowerShell knows we are using splatting.

Partial parameters

One more thing you can do with splatting is only pass in a few parameters, while still manually setting the others. Lets use our above example again:

# Create an empty hashtable
$Params = @{}

if ( $SqlUser -and $SqlPass ) {
    $Params.Username = $SqlUser
    $Params.Password = $SqlPass
}

Invoke-SqlCmd   @Params   
                -ServerInstance 'myserver.markw.dev' `
                -Query "SELECT @@VERSION" `
                -Database 'master'

In this example we are just using splatting to pass in credentials. If the $SqlUser or $SqlPass variables were not set, the hashtable would be left empty and this is absolutely fine. If the hashtable is empty the function will simply ignore it.

Conclusion

I hope you enjoyed the post. Splatting is a powerful feature and this post just scratches the surface of what you can do. If you want to read a bit more, check out the docs over at Microsoft: About Splatting