The PoSh DBA: Assigning Data to Variables Via PowerShell Common Parameters

Sometimes, it is the small improvements in a language that can make a real difference. PowerShell is able to introduce extra common parameters that can be used by any Cmdlet or advanced function. When -PipelineVariable was introduced in V4, we all wondered what it could be used for. Laerte experimented and discovered that it could be a real convenience in every-day scripting with the pipeline.

Some time ago, when PowerShell 4.0 was first released, I got a very cool script from my good friend Shay Levy. In this script I discovered the new common parameter called 'PipelineVarible‘. I studied the way it was being used, and after a bit of head-scratching, realized, “Oohh, it could be cool if I had this in my advanced functions”.

To my surprise, when I loaded my modules in PowerSell 4.0, it was listed as being there and useable in all my advanced functions. Yes. Its a common parameter so it is added for free to all existing cmdlets and advanced functions.

common Parameters

Before I explain why such an apparently obscure parameter can be so useful, I ought to explain why it was that, when I loaded my modules into PowerShell 4.0, I got the new common parameter. It is because common parameters are a set of cmdlet parameters that you can use with any cmdlet. Because they are implemented by Windows PowerShell and not by the developer, they will be avaliable in all cmdlets and advanced functions. Cool huh?

Several of these common parameters do little more than to allow you to specify a preference for the execution context of the cmdlet. These are the parameters such as-Debug, -ErrorAction, -OutBuffer, -Verbose,-WarningAction and -WarningVariable. There are others such as -Whatif and-Confirm that are considered risk mitigation parameters. In this article, we’re going to concentrate on-OutVariable and -PipelineVariable, because these seem to be less obvious, but can make a big difference to the ease of using pipelines.

These two parameters are related, but are for different purposes. They both take the name of a variable as a parameter. As such, the variable name doesn’t have the '$‘ as a prefix. You can specify, with -OutVariable, that the values assigned to the variable are added to the existing values by adding a ‘+’ prefix.


The -OutVariable parameter stores output objects from whatever command you use it with in the variable whose name you supply (without the ‘$' prefix!). It can be used in, or outside, a pipeline. It is when used in conjunction with a pipeline that it has the most obvious value.

Here we have a simple pipeline that just gives us the part to all the PowerShell files in the home directory, or its subdirectories

Now we might want to search through each of these files to find a file that, for example, uses a particular .NET library. Now, depending on the result of the search, you might want to search for a string in your SQL files in the home directory. Oh dear: you’ve just thrown away your carefully-collected file objects. You are going to have to run that slow stuff again.

Well, if you’d saved the results of the pipeline into the -OutVariable, you would still have it. Imagine it is like being able to put a tap half-way along the pipeline. You can save the output of the pipeline at that point.

Now, in this example, we can then search through our file collection a second time for a second collection of files instead of having to go out to disk. In the example, we add the list of files to the existing collection.


This new common parameter was added in PowerShell 4.0. This parameter stores the current value of the pipeline in a variable. This pipeline value will be each object that is output by the cmdlet as the objects are streamed out. It is useful because as you are executing commands in the pipeline, information gets lost along with the transformations on the objects flowing down the pipeline. It is very helpful when creating pipelines because you then don’t need to break your pipeline execution in aforeach loop in order to store pipeline values in variable.

You can shorten this parameter to -pv

Take a look of this following example, firstly without the Pipelinevareiable common parameter and then with it.

Without Pipelinevariable common parameter :

With the PipelineVariable common parameter :

With the new common parameter -PipelineVariable, this becomes

So what have we done? Did you realize that we could remove the loop statment, thereby making it easier to read? We did this by using the -PipelineVariable to save the variable $CounterCategory that we can then access in exactly the same way as a variable. We avoided having to do this by means of an assignment. This makes it much easier to run a series of functions or functions in a pipeline because we can remember the current output of every component in the pipeline.

The PipelineVariable in Action: Searching for Databases

Here is another example:

In this example, we are listing out every database beginning with ADV. ( see the line where-object { $_-like 'adv*' }). It could be any wildcard. It is a way of searching all your servers for a particular database. If you’ve never needed one of these, you’re probably not being worked hard enough by your boss. It gets complicated because we want to use our registered servers, and some of them aren’t using windows authentication. We want to list the databases, the server name, and the description of the server that we put in our registered server list (usually, where it is located).

Now without the use of the pipeline variable we would have had a lot more difficulty. We could have got as far as listing the database but we want to know which server is hosting the database. We can get the name from the $srv object but this was lost when the database names themselves were sent down the pipeline. We could have fixed that by creating a new object, via aSelect-Object cmdlet, that contained both the server name and the database name. However, we want the description of the server as well, and that is a long way down the pipeline (the get-children cmdlet provides a Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer object). Notice that we put our -PipelineVariableTheServer parameter after the sort-object cmdlet. This is because the pipeline streams only from the output of this cmdlet: put anything earlier than this and you’re going to get the wrong value in the variable.

That is guys, hope you guys liked this little explanation about these two extremely useful common parameters.