This isn’t the most interesting of blog posts admittedly. But I was using the SQL Server Management Objects (SSMO) in my PowerShell script to return a result set from a stored procedure in SQL server. The data set returned from the Invoke-sqlcmd command was a System.Object[] type, and this blog explains how to check if System.Object[] contains a value.
Check if System.Object[] contains a value
I wanted to see if a column (the Department column) in the result set contained a specific value. So firstly I obtained the result set like so:
# Load the SQL Server SMO library
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
Import-Module "sqlps" -DisableNameChecking
#SQL Server
$SQLServer = "ALKANESQLSERVER"
#Database
$SQLDatabase = "ALKANESQLDATABASE"
#Return results from stored procedure
$sql_departments = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query 'exec [dbo].[ALKANESTOREDPROCEDURE]'
In PowerShell 3 it was a trivial exercise:
if ($sql_departments."Department" -contains "AlkaneDepartment")
{
}however in PowerShell 2 it required slightly more legwork, and was a little slower to run:
if (($sql_departments | Select -ExpandProperty Department) -contains "AlkaneDepartment")
{
}To provide another example, consider we’re iterating through some local profile folders and we want to see if it contains a folder contains a certain name. We can run:
$localprofiles = get-childitem c:\Usersand check which properties it contains by running:
$localprofiles | Select *From this, we can see that the property we want to check is called name, so we simply check (using PowerShell 3+) by writing:
if ($localprofiles.Name -contains "profilename") {
#do something
}Easy!