# encoding: ascii
# api: powershell
# title: Compare-SQLResultSet
# description: A function to compare result sets from SQL Server queries. I wrote it to help with ensuring that original and modified code returned the same results. Currently only works with similarly shaped results sets (i.e. those with the same number of columns).
# version: 0.1
# type: script
# author: Josh Feierman
# license: CC0
# function: Compare-SQLResultSet
# x-poshcode-id: 3709
# x-archived: 2012-10-28T00:27:07
# x-published: 2012-10-23T07:27:00
#
#
<#
.SYNOPSIS
  Compares two result sets from SQL queries for differences.
.DESCRIPTION
  Compares the result sets from two SQL queries and outputs the
  differences.
  
  Currently the function only handles similarly shaped result sets. That is,
  result sets with the same number and names of columns. Functionality will be
  added in the future to allow for comparison and differencing of disparate result
  sets.
.PARAMETER ServerName1
  The name of the server on which the first query should be executed.
.PARAMETER DatabaseName1
  The name of the database on which the first query should be executed.
.PARAMETER Query1
  The first SQL query to be executed.
.PARAMETER ServerName2
  The name of the server on which the second query should be executed.
.PARAMETER DatabaseName2
  The name of the database on which the second query should be executed.
.PARAMETER Query2
  The second SQL query to be executed.
.EXAMPLE
  Compare-SQLResultSet -ServerName1 myServer1 -DatabaseName1 myDatabase -Query1 "exec dbo.myproc" -ServerName2 myServer2 -DatabaseName2 myDatabase -Query2 "exec dbo.myproc_changed"

  Could be used to compare two result sets from the same server and database, but two different
  procedures. Useful for comparing old and new code.
.NOTES
  Author: Josh Feierman
  Version: 1

#>
function Compare-SQLResultSet
{
  [Cmdletbinding()]
  param
  (
      [parameter(mandatory=$true)]
      [String]$ServerName1,
      [parameter(mandatory=$true)]
      [String]$DatabaseName1,
      [parameter(mandatory=$true)]
      [String]$Query1,
      [parameter(mandatory=$true)]
      [String]$ServerName2,
      [parameter(mandatory=$true)]
      [String]$DatabaseName2,
      [parameter(mandatory=$true)]
      [String]$Query2
  )
  try
  {
      if (-not (Get-Module -Name SQLPS -ListAvailable))
      {
          Write-Warning "The SQLPS module is not installed, please obtain and install it."
          return
      }
      Import-Module SQLPS
  
      #Get the two result sets
      $resultSet1 = Invoke-Sqlcmd -ServerInstance $ServerName1 -Database $DatabaseName1 -Query $Query1
      $resultSet2 = Invoke-Sqlcmd -ServerInstance $ServerName2 -Database $DatabaseName2 -Query $Query2
  
      #Get a count of records in both sets, so we can iterate over the correct number
      $firstCount = $resultSet1.Count
      $secondCount = $resultSet2.Count
  
      if ($firstCount -gt $secondCount) {$totalCount = $firstCount}
      elseif ($secondCount -gt $firstCount) {$totalCount = $secondCount}
      else {$totalCount = $firstCount}
  
      #Begin iteration
      for ($counter = 0; $counter -lt $totalCount; $counter ++)
      {
          #Get the row from the first result set
          if ($counter -lt $firstCount)
          {
              $firstRow = $resultSet1[$counter]
          }
          else
          {
              $firstRow = $null
          }
          if ($counter -lt $secondCount)
          {
              $secondRow = $resultSet2[$counter]
          }
          else
          {
              $secondRow = $null
          }
  
          #Create custom output object for comparison
          $compareObject = New-Object PSObject -Property @{
              RowNumber = $counter + 1
              ColumnName = $null
              FirstValue = $null
              SecondValue = $null
          }
  
          #Get a list of columns
          $properties = Get-Member -inputObject $firstRow -MemberType Properties
  
          foreach ($property in $properties)
          {
              $firstValue = $firstRow.Item($property.Name)
              $secondValue = $secondRow.Item($property.Name)
  
              if ($firstValue -ne $secondValue)
              {
                  $compareObject.ColumnName = $property.Name
                  $compareObject.FirstValue = $firstValue
                  $compareObject.SecondValue = $secondValue
  
                  Write-Output $compareObject
              }
          }
      }
  }
  catch
  {
      Write-Warning $_.Exception.Message
  }

}
