Get a stored procedure Output Parameters

Welcome Forums General PowerShell Q&A Get a stored procedure Output Parameters

This topic contains 13 replies, has 3 voices, and was last updated by

 
Participant
3 months, 3 weeks ago.

  • Author
    Posts
  • #131858

    Participant
    Topics: 9
    Replies: 17
    Points: 88
    Rank: Member

    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

  • #131886

    Participant
    Topics: 0
    Replies: 78
    Points: 416
    Helping Hand
    Rank: Contributor

    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.

    $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
  • #131888

    Participant
    Topics: 0
    Replies: 78
    Points: 416
    Helping Hand
    Rank: Contributor

    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.

    $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
  • #131910

    Participant
    Topics: 9
    Replies: 17
    Points: 88
    Rank: Member

    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

  • #131922

    Participant
    Topics: 0
    Replies: 78
    Points: 416
    Helping Hand
    Rank: Contributor

    · You can also format code by enclosing it in

     and 

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

    $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

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

  • #131952

    Participant
    Topics: 9
    Replies: 17
    Points: 88
    Rank: Member

    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

     

  • #131969

    Participant
    Topics: 0
    Replies: 78
    Points: 416
    Helping Hand
    Rank: Contributor

    Hope this works...

    $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
  • #131981

    Participant
    Topics: 9
    Replies: 17
    Points: 88
    Rank: Member

    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?

  • #131985

    Participant
    Topics: 0
    Replies: 78
    Points: 416
    Helping Hand
    Rank: Contributor

    Good to hear!

    
    $Message = Invoke-sqlcmd -Query $SQL-ServerInstance $DBServer -database $DB
    
    if(-not [string]::IsNullOrEmpty($Message))
    
    {
    
    # Your piece of code
    
    }
    
    
  • #132044

    Participant
    Topics: 9
    Replies: 17
    Points: 88
    Rank: Member

    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

  • #132069

    Participant
    Topics: 8
    Replies: 1190
    Points: 639
    Helping Hand
    Rank: Major Contributor

    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=}}                                                                                                                                                                                                        
    
    
  • #132099

    Participant
    Topics: 9
    Replies: 17
    Points: 88
    Rank: Member

    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?

  • #132285

    Participant
    Topics: 9
    Replies: 17
    Points: 88
    Rank: Member

    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

  • #132350

    Participant
    Topics: 8
    Replies: 1190
    Points: 639
    Helping Hand
    Rank: Major Contributor

    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.

The topic ‘Get a stored procedure Output Parameters’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort