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
– Tomalak, Commented Oct 26, 2017 at 9:30
A simple select with two joins, a c# console app using same objects returns only the rows — beaver559
– beaver559, Commented Oct 26, 2017 at 9:33
Updated the question with the sproc code, I simplified it to help factor it out — beaver559
– beaver559, Commented Oct 26, 2017 at 9:41

beaver559 · Accepted Answer · 2017-10-26 10:55:37Z

1

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  
}

answered Oct 26, 2017 at 10:55

beaver559

1442 silver badges13 bronze badges

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

2 Comments

Tomalak Over a year ago

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: "Return Value: Type: System.Int32, The number of rows successfully added to or refreshed in the DataSet.").

Tomalak Over a year ago

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.

Collectives™ on Stack Overflow

Powershell run stored procedure returns rows aswell as the input parameters

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