0

I am using the function below to execute a stored proc that return an output parmtrer.

function ProcessLogRun-Start
{
    [CmdletBinding()]
    param(
        [Parameter()]
        [string]$PackageName,
        [string]$MachineName,
        [string]$UserName
    )
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
   
    $SqlCmd.CommandText ="dbo.ProcessLogRunStart"
    $SqlCmd.CommandType=[System.Data.CommandType]'StoredProcedure'

    $p1= new-object System.Data.SqlClient.SqlParameter;
    $p1.ParameterName="@MachineName"
    $p1.DbType = [System.Data.DbType]'String';
    $p1.Size = 100
    $p1.Direction = [System.Data.ParameterDirection]'Input' 
    $p1.Value=$MachineName
    $SqlCmd.Parameters.Add($p1)>> $null;

    $p2= new-object System.Data.SqlClient.SqlParameter;
    $p2.ParameterName="@UserName"
    $p2.DbType = [System.Data.DbType]'String';
    $p2.Size = 50
    $p2.Direction = [System.Data.ParameterDirection]'Input' 
    $p2.Value=$UserName
    $SqlCmd.Parameters.Add($p2)>> $null;

    $p3= new-object System.Data.SqlClient.SqlParameter;
    $p3.ParameterName="@PackageName"
    $p3.DbType = [System.Data.DbType]'String';
    $p3.Size = 50
    $p3.Direction = [System.Data.ParameterDirection]'Input' 
    $p3.Value=$PackageName 
    $SqlCmd.Parameters.Add($p3)>> $null;

    $p4= new-object System.Data.SqlClient.SqlParameter;
    $p4.ParameterName="@RunID"
    $p4.DbType = [System.Data.DbType]'Int32';
    $p4.Direction = [System.Data.ParameterDirection]'Output' 
    $SqlCmd.Parameters.Add($p4)>> $null;

   
    $SqlConnection.Open();
    $SqlCmd.Connection=$SqlConnection;
    $SqlCmd.ExecuteNonQuery();
    $lrunId = $SqlCmd.Parameters["@RunID"].Value;
    $SqlConnection.Close();
    $SqlCmd.Dispose()
    $SqlConnection.Dispose()

    return $lrunId
    
}

Function call

$runid=ProcessLogRun-Start -PackageName $PackageName  -MachineName $env:COMPUTERNAME -UserName $env:USERNAME

The function is supposed to return a single value. However, $runid gets value as

IsPublic IsSerial Name BaseType

True True Object[] System.Array

The output of $runid

-1

6253

Why is it changing to an object array?

user1716729
  • 387
  • 1
  • 6
  • 19
  • 1
    The extra integer value comes from ExecuteNonQuery – Mathias R. Jessen Jul 28 '22 at 11:05
  • I understand -1 from ExecuteNonQuery . But my function saves the output value to $lrunId. The function only returns that value. – user1716729 Jul 28 '22 at 11:08
  • 2
    powershell is there a little different and does not only return the value of the variable which you have defined [explenation](https://stackoverflow.com/questions/10286164/function-return-value-in-powershell). Just add `| out-null` to the part of the code which returns -1 – guiwhatsthat Jul 28 '22 at 11:13
  • 3
    `return` is highly misunderstood. It's named so because it returns control of thread execution to the caller, not because it returns data – Mathias R. Jessen Jul 28 '22 at 11:15
  • Thanks @guiwhatsthat, adding out-null to ExecuteNonQuery worked. – user1716729 Jul 28 '22 at 11:19

0 Answers0