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
– Bruce Payette, Commented Jul 9, 2018 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
– Tommy Sharp, Commented Jul 10, 2018 at 1:29

Lachie White · Accepted Answer · 2018-07-09 03:35:40Z

0

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

answered Jul 9, 2018 at 3:35

Lachie White

1,2812 gold badges14 silver badges21 bronze badges

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

Comments

Tommy Sharp · Accepted Answer · 2018-07-10 01:53:13Z

0

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
}

answered Jul 10, 2018 at 1:53

Tommy Sharp

952 silver badges10 bronze badges

Collectives™ on Stack Overflow

Powershell store function returned value into a variable

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related