How to save result from SQL query in Powershell string variable?

Question

I using invoke-sqlcmd to query a database on MSSMS. This works no problem. I have a loop that runs the query X amount of times and the query returns a keycode eg. TGHDWS4.

I need this keycode as a string in powershell. I then want to add the string to an array.

The only thing i've tried is using invoke-sqlcmd. I have seen other examples on the net that use connection creation and connection close methods but i dont want to have to do that if possible.

The below code is where i'm at. How do I add the keycode (returned from the query) to an array as a string?

#Enter your confirmation numbers here;
$confArray = @(
'0000000090519838',
'0000000090059392'
)

$resultArray = @()

#Function that runs for each element in the above array
function getKeycode ($confNumber) {


$QueryFmt= "
select distinct top 100       
aa.deliveryKeycode as 'Keycode'
from appointment a with (nolock)
left join appointmentattribute aa with (nolock) on aa.appointmentid = a.appointmentid
where
a.confirmationnumber in (

'"+ $confNumber + "'

)
"

$result = Invoke-Sqlcmd -ServerInstance myserver -Database mydatabase  -Query $QueryFmt

$resultArray.Add($result)

}


#the for loop
foreach($con in $confArray){

getKeycode -confNumber $con
$count ++;

}

Mathias R. Jessen · Accepted Answer · 2019-07-02 11:28:09Z

1

I guess by just returning your array from within your function:

  # ...
  $result = Invoke-Sqlcmd -ServerInstance myserver -Database mydatabase -Query $QueryFmt 
  $resultArray.Add($result) 

  return $resultArray
}

edited Jul 2, 2019 at 11:28

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

answered Jul 2, 2019 at 11:20

JKoopman

362 bronze badges

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

1 Comment

Cormac Over a year ago

I thought because the array is declared outside the function that when i change it inside the function there is no need to return it

Mathias R. Jessen · Accepted Answer · 2019-07-02 11:34:35Z

0

Writing to an array in the parent scope from your function is a bit of an anti-pattern, I'd strongly advise against it.

Just return Invoke-SqlCmd ... directly:

function Get-Keycode ([int]$confNumber) {

  $QueryFmt= "
  select distinct top 100       
  aa.deliveryKeycode as 'Keycode'
  from appointment a with (nolock)
  left join appointmentattribute aa with (nolock) on aa.appointmentid = a.appointmentid
  where
  a.confirmationnumber in (
    '"+ $confNumber + "'
  )"

  return Invoke-Sqlcmd -ServerInstance myserver -Database mydatabase  -Query $QueryFmt

}


# Assign the output of the foreach loop to `$KeyCodes`
$KeyCodes = foreach($con in $confArray){
  Get-Keycode -confNumber $con
  $count++
}

answered Jul 2, 2019 at 11:34

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

2 Comments

Cormac Over a year ago

Thanks for you response - how do I bypass getting System.Data.DataRow as a response - when using Write-Host $KeyCodes

Mathias R. Jessen Over a year ago

DataRow's support property enumeration, so I'd do $KeyCodes.Keycode

Collectives™ on Stack Overflow

How to save result from SQL query in Powershell string variable?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related