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](https://www.alkanesolutions.co.uk/wp-content/themes/twentyeleven-child/images/application-packaging-services-blog.png)
OMG! I had the same problem. You saved my day. Thanks so much!
You’re welcome!