Powershell function string return value includes more than desired/expected

Question

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);

Can you provide sanitized sample output of Write-Host "qry out: $qryOut"? — codewario
– codewario, Commented Sep 7, 2021 at 19:44

codewario · Accepted Answer · 2021-09-08 20:23:59Z

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.

edited Sep 8, 2021 at 20:23

codewario

21.9k25 gold badges107 silver badges179 bronze badges

answered Sep 7, 2021 at 19:54

Doc

4551 gold badge5 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

codewario Over a year ago

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 Over a year ago

I suggest reading this answer of mine 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.

Collectives™ on Stack Overflow

Powershell function string return value includes more than desired/expected

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related