Powershell store function returned value into a variable

Question

I have found a great answer on Stack Overflow that explains how to have a powershell function that runs some SQL to return a single value.

What I'm not understanding is how to call the function and place the result into a variable that I can use later? Any help would be appreciated

https://stackoverflow.com/a/22715645/2461666

[string] $Server= "10.0.100.1",
[string] $Database = "Database123",
[string] $SQLQuery= $("SELECT [FeedID] FROM [dbo].[FeedList] WHERE [FeedFileName] = 'filename.txt'")

function GenericSqlQuery ($Server, $Database, $SQLQuery) {
    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $Reader = $Command.ExecuteReader()
    while ($Reader.Read()) {
         $Reader.GetValue($1)
    }
    $Connection.Close()
}

This is what I am currently trying but it doesn't seem to be working at all...

$myvariable = GenericSqlQuery

Are the values for `$server`, `$Database` and `$SQLQuery` correct for you application? I suspect not as they are exactly the same as the values in the linked example. Make sure the values are correct for your database and schema then try again. — Bruce Payette, Jul 09 '18 at 21:58
Apologies, yes in my environment I am specifying the correct server details. I've added some logging to my function so I can confirm that it is properly querying the database and returning the value I need.... I'm just having an issue with calling the function and saving it's result to a variable... — Tommy Sharp, Jul 10 '18 at 01:29

score 0 · Answer 1 · answered Jul 09 '18 at 03:35

Awesome to see you searched stack overflow for an answer first Tommy!

Think I found another one to help you!

I have found something that may help:
powershell function output to variable

Below is from the above link

function getip {
    $strComputer = "computername"

    $colItems = GWMI -cl "Win32_NetworkAdapterConfiguration" -name "root\CimV2" -comp $strComputer -filter "IpEnabled = TRUE"

    ForEach ($objItem in $colItems) {
        $objItem.IpAddress
    }
}


$ipaddress = getip

score 0 · Accepted Answer · answered Jul 10 '18 at 01:53

Okay, the way I have got it to work for me in this situation is to pass the value to a variable within my function and then within my function return the variable like this....

while ($Reader.Read()) {
     $SQLResult = $Reader.GetValue($1) 
     return $SQLResult
}

Powershell store function returned value into a variable

2 Answers2