CODEWITHSUNDEEP
sqlpowershellado
beaver559
beaver559

Reputation: 144

Powershell run stored procedure returns rows aswell as the input parameters

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

Upvotes: 1

Views: 3507

Answers (1)

beaver559
beaver559

Reputation: 144

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  
}

Upvotes: 1

Related Questions