PowerShell for Windows Admins

Jan 29 2015   12:15PM GMT

Variable select

Richard Siddaway Richard Siddaway Profile: Richard Siddaway


I was working on some code that  accesses a SQL database this afternoon. I only needed to pull back a single column from a single row but which column to pull back is variable depending on other data.

That’s OK

$query = “SELECT $colname FROM tablename WHERE x = ‘y’”

Invoke-SQLcmd –server <server> –database <database> –query $query

Now the problem hit me as I need to get the actual value from the object that invoke-sqlcmd returns

I normally do this:

Invoke-SQLcmd –server <server> –database <database> –query $query | select –expandproperty <columnname>

And then it dawned on me that I have the column name in $colname so this works

Invoke-SQLcmd –server <server> –database <database> –query $query | select –expandproperty $colname

I got so used to explicitly stating the properties I need that I forgot you could use a variable.  If you want an example to try on any system

Get-Service | select -First 1 | select -ExpandProperty $p1

or you could try

$p1 = ‘Status’
Get-Service | select Name, $p1

and change to

$p1 = ‘DisplayName’
Get-Service | select Name, $p1

Not something you want to do every day but a useful trick when you need it

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: