How to get stored procedure output parameter into variable using Powershell?

Question

I have a stored procedure returning a string and I need the result as a powershell variable. I'm stuck on the output parameter syntax.

Powershell script:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=myserver;Database=mydb;Integrated Security=True"
$SqlConnection.Open()
$SqlConnection.State

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "testsp3"
$SqlCmd.Connection = $SqlConnection


$op = new-object System.Data.SqlClient.SqlParameter;
$op.ParameterName = "@answer";
$op.Direction = [System.Data.ParameterDirection]'Output';
$op.DbType = [System.Data.DbType]'String';
$op.Size = 2500

$SqlCmd.Parameters.Add($op);

$what = $SqlCmd.ExecuteScalar();

$what

Error message:

Exception calling "ExecuteScalar" with "0" argument(s): "Procedure or function 'testsp3' expects parameter '@answer', which was not supplied."

Stored procedure if it matters:

CREATE PROCEDURE [dbo].[testsp3]
@answer nvarchar(max) output

AS
BEGIN
    SET NOCOUNT ON;

    SELECT @answer = 'blah blah blah'
    RETURN
END
GO

Answer 1

Modify your script to be like below

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=myserver;Database=mydb;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "testsp3"
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandType = [System.Data.CommandType]'StoredProcedure'; <-- Missing
$outParameter = new-object System.Data.SqlClient.SqlParameter;
$outParameter.ParameterName = "@answer";
$outParameter.Direction = [System.Data.ParameterDirection]'Output';
$outParameter.DbType = [System.Data.DbType]'String';
$outParameter.Size = 2500;
$SqlCmd.Parameters.Add($outParameter) >> $null;
$SqlConnection.Open();
$result = $SqlCmd.ExecuteNonQuery();
$truth = $SqlCmd.Parameters["@answer"].Value;
$SqlConnection.Close();
$truth;