[PowerShell] ストアド実行とエラーの検知

したいこと

SQLServerでストアドを実行して、エラーメッセージを取得する
ストアドの実行はできても、ストアド側のエラーメッセージを取得できなくてはまったのでメモ

使用するコマンド

コマンドレット	実行内容
Invoke-Sqlcmd	SQLを実行

フロー

1. 除算の結果をテーブルにinsertするストアドを作成
2. 0除算エラーを起こすようにパラメータに0をセットしてPowerShellを実行

ストアドサンプル

CREATE PROCEDURE [dbo].[usp_test]
(
	@val			int,
	@err_no			int = 0 OUTPUT,
	@err_msg		varchar(500)= NULL OUTPUT
)
AS
SET NOCOUNT ON

BEGIN TRY
	INSERT INTO tbl_test
	VALUES(1/@val)
END TRY

BEGIN CATCH
	SET @err_no = ERROR_NUMBER()
	SET @err_msg = ERROR_MESSAGE() 
	RETURN 
END CATCH

SET @err_no = 0
SET @err_msg = NULL

psサンプル

# Server
$strServer = $env:COMPUTERNAME
# DB
$strDB = 'TEST'
# パラメータ
$val = 0
# SQL
$strSQL = @"
DECLARE	@return_value int,
        @val int,
        @err_no int,
        @err_msg varchar(500)

EXEC	@return_value = [dbo].[usp_test]
        @val = 0,
        @err_no = @err_no OUTPUT,
        @err_msg = @err_msg OUTPUT

SELECT	'Return Value' = @return_value, 
        'ErrorMessage' = @err_msg        ← ここでエラーメッセージが出るようにしておく
"@

$ret = Invoke-Sqlcmd -ServerInstance $strServer -Database $strDB -Query $strSQL

if($ret.'Return Value' -ne 0){
        Write-Host "ストアドプロシージャ 実行エラー "$ret.ErrorMessage　　#ここができなくてハマった
        $retcode = 8
    }else{
        Write-Host "ストアドプロシージャ 実行OK" 
        $retcode = 8  
}