Monthly Archive

Categories

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

Comments are closed.