header image

Variable select

Posted by: | January 29, 2015 Comments Off on Variable select |

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

under: PowerShell Basics

Comments are closed.