I was recently working on a PowerShell script to set up some new databases for my users and found myself writing the same things over and over again.
Invoke-DbaQuery -SqlInstance MyServer -Database $SrcDB -Query "select field1 from table..."; Invoke-DbaQuery -SqlInstance MyServer -Database $DstDB -Query "update table..."; Invoke-DbaQuery -SqlInstance MyServer -Database $DstDB -Query "insert into table...";
By the 4th
Invoke-DbaQuery, I found myself thinking “this repetitive typing kind of sucks.” Then I remembered Chrissy LeMaire’s segment in the first PSPowerHour where she talked about default values, and her accompanying dbatools blog post. Most of the blog posts and demos of this feature focus on using it from the command line, so I had overlooked the fact that I could use it from within a script as well, and even change the values when looping.
As it turns out – it works inside scripts and functions as well, and can make them a lot easier to read. And you’re not limited to the default parameters every time you call a given function; you can override the defaults by specifying the parameters when you call it.
$PSDefaultParameters is a hash, and the name/value pairs take the format of
'FunctionName:ParameterName' = ParameterValue.
I set the destination database as the default for
Invoke-DbaQuery because there will be a number of additional queries I need to execute against that database. In Step 1, I’m using the parameter defaults for both
SqlInstance, so I don’t need to specify them when calling the function. In Step 2, I need to override only
Database, so I’m just specifying that one parameter.
Word of warning: Because
$PSDefaultParameters is a hash, you might want to “build up” defaults over the course of a function, script or session. In that case, get in the habit of appending to the hash so that you don’t overwrite the defaults you’ve previously set.
If you’re already splatting parameters, this is a feature you can take advantage of to take the next step in tidying up your scripts and making them easier to read & manage.