In a prior blog, I demonstrated how to call a SQL Stored Proc from Powershell.  But that article did not discuss error handling.

In the stored proc, it stored two tables in a parent/child relationship.  If the parent “key” was not found, then the child row cannot be stored. The parent is the based on the customer “license” code.  So in the example below, I pass “bad license” in the $license variable.

Powershell detects the errors, shows it in red, and yet continues to run.

Results with error

If you want to catch the error, handle it some special way, then continue or stop, you do that with the try/catch statement.

Sample Powershell Code with Try/Catch

CLS
#$license = "4F926ADB-8193-4824-9881-DF147B721889"
$license = "bad license"
$keyword = "test"
$computerName = "PC45"
$videoFilename = "c:\videos\abcdefg.mp4"
$templateName = "Customer_Appliance4.prj"
$getUTCdate = $(get-date).ToUniversalTime()

try
{
  LogVideoTrackingToSQL $license $keyword $computerName $videoFilename $templateName $getUTCdate $getUTCdate
}
catch
{
   # Handle the error
   $err = $_.Exception
   Write-Host $err.Message
   #There could possibly be multiple inner exceptions but not in this example. 
   while( $err.InnerException ) 
     {
        $err = $err.InnerException
        Write-Host "InnerException: $($err.Message)" 
     }
}
finally 
{
     write-output "script completed"
}

Results with Catch error

Notice that all the text is white on blue, indicating we caught the error, and printed it in a regular Write-Host statement.

The full code of the stored proc was included in the prior blog. Here are the lines of the Stored Proc that generated the above error:

  select @CustomerID = ID from Customer C where C.LicenseGuid = @License 

  if @CustomerID is null 
    BEGIN  
      SET @ErrMessage = 'No customer license found for key=' + @License 
      RAISERROR (@ErrMessage, 11,1)
    END

I could have also put the catch error in the function itself, and returned a better $result variable.