Get a stored procedure Output Parameters

I have a PS script which runs fine and execute correctly a SP against SQL except that I am not able to get back the output parameter.

My PS script is as follows

$sqlstatementInsert =@' exec [ACTIVE].[uspUpsertStocks] @Symbol = '{0}', @st_date = '{1}', @ST_OPEN = '{2}', @ST_HIGH = '{3}', @ST_LOW = '{4}', @ST_CLOSE = '{5}', @ST_VOLUME = '{6}', @ST_ADJ_CLOSE = '{7}', @msg = '{8}' '@

$i = 0

$list = Get-ChildItem -Path $csvpath | Select name

ForEach ($file in $list) {
[string]$backMsgFromSql = ‘’
$csvpathSource = $csvpath + $file.name #| Get-Content $csvpathSource

$content = Import-Csv -Path $csvpathSource -Delimiter ‘,’
$content | ForEach-Object {
$backMsgFromSql = ‘’

$SQL = $sqlstatementInsert -f $.symbol, $.date,$.open, $.high, $.low, $.close, $.volume,$.adjclose, “$backMsgFromSql output”
Invoke-sqlcmd -Query $SQL -ServerInstance $DBServer -database $DB
$i = $i + 1
Write-Host “$backMsgFromSql”
}
Write-Host $i
}


 

Stored proc inside sql works fine inside SQL and returns the output but in PS it is always empty even though SP executed correctly, here is the 1st part of the SP. Thanks for your help

PROCEDURE [ACTIVE].[uspUpsertStocks] @Symbol [varchar] (32), @st_date date, @ST_OPEN real, @ST_HIGH real, @ST_LOW real, @ST_CLOSE real, @ST_VOLUME bigint, @ST_ADJ_CLOSE real, @MSG [varchar] (32) output

Hello Salam,

Please elaborate a bit about what you are trying to achieve and add some comments to the code. You should use double quotes when you want to use variables/parameters inside the string, and I not sure why are you using $backMsgFromSql variable. However, I tweaked your code a bit and give it a try. Thank you.

[pre]

$sqlstatementInsert =@"
exec [ACTIVE].[uspUpsertStocks] @Symbol = '{0}', @st_date = '{1}', @ST_OPEN = '{2}', @ST_HIGH = '{3}', @ST_LOW = '{4}', @ST_CLOSE = '{5}', @ST_VOLUME = '{6}', @ST_ADJ_CLOSE = '{7}', @msg = '{8}'
"@
$i = 0
$list = Get-ChildItem -Path $csvpath | Select name
ForEach ($file in $list) {
[string]$backMsgFromSql = ''
$csvpathSource=$csvpath+$file.name#| Get-Content $csvpathSource
$content=Import-Csv-Path $csvpathSource-Delimiter ','
$content|ForEach-Object {
$backMsgFromSql=''
$SQL=$sqlstatementInsert-f $_.symbol, $_.date,$_.open, $_.high, $_.low, $_.close, $_.volume,$_.adjclose,"$backMsgFromSql output"
Invoke-sqlcmd-Query $SQL-ServerInstance $DBServer-database $DB
$i=$i+1
Write-Host"$backMsgFromSql"
}
}
Write-Host $i
[/pre]

Don’t know why my previous post is not visible here, however, I am echoing…

Please elaborate a bit about what you are trying to achieve and add some comments to the code. You should use double quotes to the string when you want to use variables/parameters inside the string. And not sure what is the use of $backMsgFromSql variable in the code. however, I have tewaked your code a bit and give it a try. Thank you.

[pre]

$sqlstatementInsert =@"
exec [ACTIVE].[uspUpsertStocks] @Symbol = '{0}', @st_date = '{1}', @ST_OPEN = '{2}', @ST_HIGH = '{3}', @ST_LOW = '{4}', @ST_CLOSE = '{5}', @ST_VOLUME = '{6}', @ST_ADJ_CLOSE = '{7}', @msg = '{8}'
"@
$i = 0
$list = Get-ChildItem -Path $csvpath | Select name
ForEach ($file in $list) {
[string]$backMsgFromSql = ''
$csvpathSource=$csvpath+$file.name#| Get-Content $csvpathSource
$content=Import-Csv-Path $csvpathSource-Delimiter ','
$content|ForEach-Object {
$backMsgFromSql=''
$SQL=$sqlstatementInsert-f $_.symbol, $_.date,$_.open, $_.high, $_.low, $_.close, $_.volume,$_.adjclose,"$backMsgFromSql output"
Invoke-sqlcmd-Query $SQL-ServerInstance $DBServer-database $DB
$i=$i+1
Write-Host"$backMsgFromSql"
}
}
Write-Host $i
[/pre]

Thanks Kiran. to get back the value for an output parameter in a Stored procedure, in T-SQL inside sql sever management, we need to do the following

declare @msg1 [varchar] (32) = ‘’

exec [ACTIVE].[uspUpsertStocks] @Symbol = ‘A’, @st_date = ‘2016-12-30’, @ST_OPEN = 45.76, @ST_HIGH = 45.82, @ST_LOW = 45.38, @ST_CLOSE = 45.56, @ST_VOLUME = 1216100, @ST_ADJ_CLOSE = 44.84896, @msg = @msg1 output
Print @msg1

so I am trying to replicat this in Powershell code. Cany you please tell me how you format the code in a special window, 2nd what did you tweak?

What I trying to do is to loop a bunch of files, in each loop I loop through the records and call the SP which checks if a row exists, it sends back a message indicating that record exist, otherwise it does an insert, a sort of upsert

· You can also format code by enclosing it in [pre] and [/pre]

As I mentioned earlier, you should use double quotes to the string when you want to use variables/parameters inside the string.

[pre]$sqlstatementInsert = @"
exec [ACTIVE].[uspUpsertStocks] @Symbol = ‘{0}’, @st_date = ‘{1}’, @ST_OPEN = ‘{2}’, @ST_HIGH = ‘{3}’, @ST_LOW = ‘{4}’, @ST_CLOSE = ‘{5}’, @ST_VOLUME = ‘{6}’, @ST_ADJ_CLOSE = ‘{7}’, @msg = ‘{8}’
“@
$i = 0
$list = Get-ChildItem -Path $csvpath | Select name
ForEach ($file in $list) {
[string]$backMsgFromSql = “”
$csvpathSource = $csvpath + $file.name #| Get-Content $csvpathSource
$content = Import-Csv -Path $csvpathSource -Delimiter ‘,’
$content|ForEach-Object {
$backMsgFromSql = “”
$SQL = $sqlstatementInsert-f $.symbol, $.date,$.open, $.high, $.low, $.close, $.volume,$.adjclose,”$backMsgFromSql output"
Invoke-sqlcmd -Query $SQL-ServerInstance $DBServer -database $DB
$i = $i + 1
Write-Host “$backMsgFromSql”
}
}
Write-Host $i[/pre]

Test this code and and let’s see. Thank you.

Yes I did, but still output parameter is not coming back, in fact tha call should understand that this is a variable e to be populated by the SP which is not the case, always empty. I tried another approach by using as in C#, using .Net objects, using sqlcmd parameters collection, last one is the output, and I am getting back the output correctly from sql but execution time is 4 times slower than using Invoke-sqlcmd in the 1st version of code

 

Hope this works…

[pre]

$sqlstatementInsert = @"
Declare @Message nvarchar(50);
exec [ACTIVE].[uspUpsertStocks] @Symbol = '{0}', @st_date = '{1}', @ST_OPEN = '{2}', @ST_HIGH = '{3}', @ST_LOW = '{4}', @ST_CLOSE = '{5}', @ST_VOLUME = '{6}', @ST_ADJ_CLOSE = '{7}', @msg = @Message output;
Select @Message;
"@
$i = 0
$list = Get-ChildItem -Path $csvpath | Select name
ForEach ($file in $list) {
$csvpathSource = $csvpath + $file.name #| Get-Content $csvpathSource
$content = Import-Csv -Path $csvpathSource -Delimiter ','
$content|ForEach-Object {
$SQL = $sqlstatementInsert -f $_.symbol, $_.date,$_.open, $_.high, $_.low, $_.close, $_.volume,$_.adjclose
Invoke-sqlcmd -Query $SQL-ServerInstance $DBServer -database $DB # Default output
$i = $i + 1
}
}
Write-Host $i
[/pre]

So many thanks, Ok, it works, but how can I get get back the value put it in avariable in order to use it in an if statement?

Good to hear!

[pre]

$Message = Invoke-sqlcmd -Query $SQL-ServerInstance $DBServer -database $DB

if(-not [string]::IsNullOrEmpty($Message))

{

Your piece of code

}

[/pre]

Thanks again, while I was waiting for your response, I searched Get-Help for Invoke-sqlcmd, I understood that it returns an array, I found one of the examples interesting for me. I updated my code as follows:

$SQL = $sqlstatementInsert -f $_.symbol, $_.date,$_.open, $_.high, $_.low, $_.close, $_.volume,$_.adjclose, "$backMsgFromSql output" 

$Tables = Invoke-sqlcmd -Query $SQL -ServerInstance $DBServer -database $DB -As DataTables

if(-not [string]::IsNullOrEmpty($Tables)) {
$messageBackFromSql = $Tables[0].Rows.Item(0)
$messageBack = $messageBackFromSql.Item(0)


If ($messageBack.Equals("Symbol exist")) {
$iExist = $iExist + 1
Write-Host "$messageBack"
} Elseif ($messageBack.Equals("Inserted")) {

$i = $i + 1
Write-Host "$_.symbol + $messageBack"
} Else {

Write-Host "$messageBack"
}
} Else {
Write-Host "Unknown Error"
}

It works fine. In fact, executing Invoke-sqlcmd does not return a string. However, I have a last Issue on the line

Write-Host "$_.symbol + $messageBack"

instead of printing the value of $_.symbol, it prints the whole line

$SQL = $sqlstatementInsert -f $.symbol, $.date,$.open, $.high, $.low, $.close, $.volume,$.adjclose, “$backMsgFromSql output”

as follows

@{symbol=A; date=12/30/2016; open=45.76; high=45.82; low=45.38; close=45.56; adjclose=44.73833; volume=1216100}.symbol

  • Inserted

You may be over complicating things. PRINT is a debugging method rather than sending something as output, so if you used the -Verbose switch when you called Invoke-SqlCmd, you would see the PRINT information in verbose output. As @Kiran indicated, you need to use SELECT to send data back. By default, Invoke-SQLCmd is going to send a PSObject back, so unless you were trying to send multiple selects back at once which would be multiple tables, then you don’t need to use datatables. My guess is you had issues getting the variable because you didn’t name it in your Select, so it’s easier to assign an alias (e.g. Select @var AS friendlyName).

It appears you’re calling a stored procedure that does and INSERT\UPDATE and returns a message for each symbol. You could do this multiple ways, one way is to pass the data to SQL and then have SQL pass everything back, like so:

$sqlstatementInsert = @"
DECLARE @Message nvarchar(50);
EXEC [ACTIVE].[uspUpsertStocks] @Symbol = '{0}'
                               ,@st_date = '{1}'
                               ,@ST_OPEN = '{2}'
                               ,@ST_HIGH = '{3}'
                               ,@ST_LOW  = '{4}'
                               ,@ST_CLOSE = '{5}'
                               ,@ST_VOLUME = '{6}'
                               ,@ST_ADJ_CLOSE = '{7}'
                               ,@msg = @Message output;
SELECT @Message AS Message
      ,@Symbol AS Symbol
      ,@st_date AS Date
...

"@

When you get the results back from SQL, you would have everything already in a PSObject. Another method is to basically glue it together in Powershell using a calculated expression, like so:

$sqlstatementInsert = @"
DECLARE @Message nvarchar(50);
EXEC [ACTIVE].[uspUpsertStocks] @Symbol = '{0}'
                               ,@st_date = '{1}'
                               ,@ST_OPEN = '{2}'
                               ,@ST_HIGH = '{3}'
                               ,@ST_LOW  = '{4}'
                               ,@ST_CLOSE = '{5}'
                               ,@ST_VOLUME = '{6}'
                               ,@ST_ADJ_CLOSE = '{7}'
                               ,@msg = @Message output;
SELECT @Message AS Message
"@


$list = Get-ChildItem -Path $csvpath | Select FullName

$results = forEach ($file in $list) {
    $content = Import-Csv -Path $file.FullName

    foreach ($symbol in $content)  {
        $params = @{
            Query = $sqlstatementInsert -f $_.symbol, $_.date,$_.open, $_.high, $_.low, $_.close, $_.volume,$_.adjclose
            ServerInstance = $DBServer 
            Database = $DB
            
        }
        
        $sqlResults = Invoke-SqlCmd @params

    }

    $file |
    Select *,
           @{Name='Message';Expression={$sqlResults.Message}}
}

$results

In any event, you want to return all of the data and append the message so you can see what the result was of passing that data. Then you don’t need all of the if statements and you can use grouping or where statements to see what happened with your batch:

$results | Group-Object -Property Message




Count Name                      Group                                                                                                                                                                                                                            
----- ----                      -----                                                                                                                                                                                                                            
    1 Inserted                  {@{Symbol=ABC; Message=Inserted}}                                                                                                                                                                                                
    2 Symbol exist              {@{Symbol=SUB; Message=Symbol exist}, @{Symbol=ZXR; Message=Symbol exist}}                                                                                                                                                       
    1                           {@{Symbol=GTF; Message=}}                                                                                                                                                                                                        

Rob, thanks for your explanation, however, what I needed from SP is

  • Either update or insert
  • sends back weather "Symbol Exists" or "Inserted" (as I plan to log those details later to a file or to a sql table)
Yes, I forgot to do a Select @Msg in my Stored proc which lead me to spend time and thought it was a PS issue. Back to my question and would love to have your answer: while looping I am fetching variables in the following statement
 Query = $sqlstatementInsert -f $_.symbol, $_.date,$_.open, $_.high, $_.low, $_.close, $_.volume,$_.adjclose
then once a symbol is inserted, I would like to

write-host “$_.symbol + inserted”

 

why it is writing the whole thing?

@{symbol=AAL; date=12/30/2016; open=47.42; high=47.66; low=46.47; close=46.69; adjclose=45.84765; volume=4495000}.symbol + Inserted

you agree with me that in the query statement it is only the value that is replaced not the whole thing, non?

Rob, I tried your 2nd suggestion “using a calculated expression”, nothing gets executed, I ran sql profiler, I have only

EXEC [ACTIVE].[uspUpsertStocks] @Symbol = ''
,@st_date = ''
,@ST_OPEN = ''
,@ST_HIGH = ''
,@ST_LOW = ''
,@ST_CLOSE = ''
,@ST_VOLUME = ''
,@ST_ADJ_CLOSE = ''
,@msg = @Message output

SELECT @Message AS Message

and this get repeated with no sql executed against the database

It’s actually the similar to the issue you were having before. The loop was updated to foreach ($symbol in $content), so we need to update the reference to $_ to $symbol:

Query = ($sqlstatementInsert -f $symbol.symbol, $symbol.date,$symbol.open, $symbol.high, $symbol.low, $symbol.close, $symbol.volume,$symbol.adjclose)

To answer your original question, $_ is a reference to the current object in a loop. You would need to execute the code within the loop, something like this:

$content|ForEach-Object {
    $SQL = $sqlstatementInsert -f $_.symbol, $_.date,$_.open, $_.high, $_.low, $_.close, $_.volume,$_.adjclose
    $messageBack = Invoke-sqlcmd -Query $SQL-ServerInstance $DBServer -database $DB # Default output
    $i = $i + 1

    ...
    Write-Host "$_.symbol + $messageBack"
    ...
}

However, if you get the code I posted working it’s going to show you the result and all of the data that was passed to SQL versus trying to manually run if statements. Returning it as a PSObject you could do analysis and even export the results to a CSV later.