1

Why does the simplified function below return more than the fruit name in the returned string?

For example, the write host INSIDE the function shows a fruit name. However the write host at the bottom (outside the function) shows fruit name (as expected) plus the database connection info (which is NOT desired/expected)

    #inside the function, write-host shows a fruit name as expected
    #outside the function (bottom line below) write-host shows extra things such as the connection string (for example: "server=myserver;database=mydb;userid=myuser....thefruitname")
    
    function Get_A_SINGLE_FRUIT_NAME{
        $queryString = "SELECT top 1 fruitname from FROM fruits"; 
        $dbConnectionString = "a-real-conn-string-was-here"   

        $dbConnectionString

        Invoke-Sqlcmd -ConnectionString $edwConnectionString -Query $queryString -MaxCharLength 50000 -OutVariable sqlReturn |Out-Null   
    
        foreach($queryResultRow in $sqlReturn){
            try{
                [String]$returnString = $queryResultRow.fruitname
                write-host("fruitname found:"+$returnString);
            }Catch{
                $_.Exception.Message
                $returnString="No fruitname found"
            }
            Break
        }
        return $returnString 
    }


$qryOut = Get_A_SINGLE_FRUIT_NAME
write-host("qry out:"+$qryOut);
Doc
  • 435
  • 1
  • 5
  • 15

1 Answers1

3

I found the issue. When a PowerShell function returns a value, it also returns any other values output to the pipeline which may have occurred inside the function before the return statement.

See also: Function return value in PowerShell

So I removed the extraneous line which contained ONLY $dbConnectionString, and now the function returns ONLY the expected value.

codewario
  • 19,553
  • 20
  • 90
  • 159
Doc
  • 435
  • 1
  • 5
  • 15
  • I was thinking something like this was the problem, though I was thinking perhaps `Invoke-SqlCmd` might return connection information. In this case it was the `$dbConnectionString` variable which was omitted in your original sample. – codewario Sep 07 '21 at 19:57
  • I suggest reading this [answer of mine](https://stackoverflow.com/a/58615047/584676) to learn about output streams and redirection. You can use additional cmdlets to display information on the screen that isn't operable (without some tweaking) programmatically. E.g. you could display your connection string if you want without having it returned via the pipeline and assigned to your variable. – codewario Sep 07 '21 at 19:58