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")
{

}

 

 

Check if System.Object[] contains a value

2 thoughts on “Check if System.Object[] contains a value

Comments have now been disabled. If you have a question to ask about this post please ask the community!