powershell - Returning Error code and event from SQL Server procedure call -

i trying setup powershell script run sql server stored procedures. initially, script returned either 0 or non-zero return code (by evaluating catch block), depending on whether procedure call failed or not:

try {         $command = new-object system.data.sqlclient.sqlcommand ("$query", $connection)                   $dataadapter = new-object system.data.sqlclient.sqldataadapter $command         $dataset = new-object system.data.dataset          $rowcount = $dataadapter.fill($dataset)         if($rowcount -gt 0) {             $dataadapter.fill($dataset) | out-null             $return =,[int]$command.parameters["@returnvalue"].value             $return += ,$dataset.tables[0].rows[0]         } else{             $return=,[int]$command.parameters["@returnvalue"].value                      }     }     catch {         $return=,10000         throw     } 

now want make bit more usable , return error message if job fails. this, using system.data.sqlclient.sqlinfomessageeventhandler, seems work, except doesn't fall catch block , therefore doesn't evaluate other return code. code (the full function):

function run-sqlquery($datasource, $database, $query) { $stuffedmessage = "" $handler = [system.data.sqlclient.sqlinfomessageeventhandler] {param($sender, $error) $stuffedmessage +=  "$($_)" };     try {            try {         $connectionstring = "data source=$datasource; " +             "integrated security=sspi; " +             "initial catalog=$database"          # create connection olap database         $connection = new-object system.data.sqlclient.sqlconnection($connectionstring)                  $connection.add_infomessage($handler);          $connection.fireinfomessageeventonusererrors = $true;         $connection.open()     }     catch {         $return = ,"unable open connection"         throw     }      try {         $command = new-object system.data.sqlclient.sqlcommand ("$query", $connection)          $dataadapter = new-object system.data.sqlclient.sqldataadapter $command         $dataset = new-object system.data.dataset          $rowcount = $dataadapter.fill($dataset)         $return = ,$stuffedmessage         $return +=[int]$command.parameters["@returnvalue"].value         if($rowcount -gt 0) {              $dataadapter.fill($dataset) | out-null             $return +=[int]$command.parameters["@returnvalue"].value             $return +=$dataset.tables[0].rows[0]         } else{             $return +=[int]$command.parameters["@returnvalue"].value                         }     }     catch {         $return=,10000         throw     }            $connection.close() } catch { } {     $connection.dispose()     ,$return } 

i don't want evaluate return message determine whether proc has errored out. can recommend method or way both capture message full proof way show proc call has either been successful or failure?

cheers