Powershell run stored procedure returns rows aswell as the input parameters

Question

I have a stored procedure which takes a date as a paramter and returns rows, in the return from the function in powershell an object array is returned containing the parameters and the rows.

I know I can get around this by referencing the rows and working with them but I want to know why it returns the parameter. Can anyone shed some light please?

function Invoke-SQL($dataSource, $database, $sqlCommand, $tradeDate) {

    $result = New-Object System.Data.DataTable     

    $connectionString = "Data Source=$dataSource; Integrated Security=SSPI; Initial Catalog=$database"
    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)

    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $command.CommandType = [System.Data.CommandType]::StoredProcedure

    $parameter = New-Object System.Data.SqlClient.SqlParameter ("@TradeDateParam", $tradeDate)
    $command.Parameters.Add($parameter);  

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

    $connection.Open()   
    $adapter.Fill($result) | Out-Null
    $connection.Close()

    return $result
}

$resultsDataTable = New-Object system.Data.DataTable 
$resultsDataTable = Invoke-SQL "server" "db" "sproc" "dateparam"



ALTER PROCEDURE [dbo].[spCheckTradeActivityBetweenTradeDates]   
    @TradeDateParam date = null
AS

DECLARE @TradeDate date = @TradeDateParam;

SELECT 'HEllO' AS hello
return 0

Image of returned object array

What does the stored procedure look like? Does it return more than one thing? — Tomalak, Oct 26 '17 at 09:30
A simple select with two joins, a c# console app using same objects returns only the rows — beaver559, Oct 26 '17 at 09:33
Updated the question with the sproc code, I simplified it to help factor it out — beaver559, Oct 26 '17 at 09:41

score 1 · Accepted Answer · answered Oct 26 '17 at 10:55

So after some reading on Michael Sorens answer it appears functions in Powershell can sometimes return more output than the value you are returning. I used an Out-Null pipe to suppress the output of the paramter as you can see below, it is worth noting that the Out-Null pipe is not the most efficient way to handle this.

Whilst the pipe does work I still do not know why it added a parameter to a SQL command generates output.

function Invoke-SQL($dataSource, $database, $sqlCommand, $tradeDate) {

    $result = New-Object System.Data.DataSet    

    $connectionString = "Data Source=$dataSource; Integrated Security=SSPI; Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)


    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand, $connection)
    $command.CommandType = [System.Data.CommandType]::StoredProcedure

    # $parameter = New-Object System.Data.SqlClient.SqlParameter ("@TradeDateParam", $tradeDate)
    $command.Parameters.AddWithValue("@TradeDateParam", $tradeDate) | Out-Null  # <--Added this pipe to suppress parameter output

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

    $connection.Open()   
    $adapter.Fill($result) | Out-Null
    $connection.Close()

    return $result.Tables  
}

`Out-Null` is fine, another way of doing it is to assign the result to a variable that you never use - or, like in this case - use for its intended purpose of success checking (from the [docs](https://msdn.microsoft.com/en-us/library/377a8x4t(v=vs.110).aspx): *"Return Value: Type: `System.Int32`, The number of rows successfully added to or refreshed in the DataSet."*). — Tomalak, Oct 26 '17 at 11:27
It's worth noting that the behavior you see is true for *any* Powershell Script block. Any value that is produced inside the script block and not either stored in a variable or piped into `Out-Null` will become part of the return value of that script block. This produces three values: `{1; 2; 3}`, this only two: `{1; $foo = 2; 3}`. Keep an eye out for it, it will bite you in the future. — Tomalak, Oct 26 '17 at 11:30

Powershell run stored procedure returns rows aswell as the input parameters

1 Answers1