As I’ve been playing around updating my scripts to functions I found that understanding the basics of scopes is pretty important! and if you forget about them for say an hour on Tuesday afternoon after lunch.. you may well go around in circles like i did! with that lets look at the basics of Scopes in functions.

The below script is a simple example of what were i went wrong and hopefully my foolish example will guide you not to make the same mistake. In our script we want to know the number of database’s in an instance, to start out with, we just run the below and update $Server when needed

$Server = "dev-sql01"
$Query = "SELECT COUNT(*) FROM sys.databases"

IF (!(Get-Module -Name sqlps))
    {
        Push-Location
        Import-Module sqlps -DisableNameChecking
        Pop-Location
    }

TRY
    {
        $Result = Invoke-Sqlcmd -ServerInstance $Server -Query $Query
    }
CATCH
    {
        "Error connecting to server"
    }

$Result

PowerShellScope

Now lets say we want to update this to function! lets face it, its pretty damn handy!

So to update it we first wrap it in

FUNCTION Get-SQLDatabaseCount {

}

and lets make that Server input an parameter to the Function

FUNCTION Get-SQLDatabaseCount{
        [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)]
        [string]$Server
        )
}

So now the scripts looks like this

FUNCTION Get-SQLDatabaseCount{

        [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)]
        [string]$Server
        )
     

$Query = "SELECT COUNT(*) FROM sys.databases"

IF (!(Get-Module -Name sqlps))
    {
        Push-Location
        Import-Module sqlps -DisableNameChecking
        Pop-Location
    }

TRY
    {
        $Result = Invoke-Sqlcmd -ServerInstance $Server -Query $Query
    }
CATCH
    {
        "Error connecting to server"
    }

$Result

}

Now if we run it, we can use

Get-SQLDatabaseCount -Server "DEV-SQL01"

Which works and we are happy and every is good!

PowerShellScope02

But lets say we want to update it and don’t want to count them silly system databases (Who needs them anyway!) So we update our $Query to

$Query = "SELECT COUNT(*) FROM sys.databases WHERE Database_ID > 4"

We run it and get 4 But for the sake of argument lets say you think it should be 5! (This is were i went wrong! so im making up a reason to look at them variables in the function)

PowerShellScope03

so you just highlight $Result and run it..

Now this is the point we have been working to! What do you get? 4 right?

.

…..
……….

PowerShellScope04

8! and that’s Scope.. As soon as you wrap all them variables in a Function they only exist in that function (Private scope). When we run $Result we are get the $Result of the Global scope which is still the value returned from the time we ran the script in its original  version (pre function).

This is the issue, if you’r like me and work via writing out a script get it working and then update it to a function, if you try and troubleshoot it by running the function and reading the variables you create after you will never get the results from the function.

So whats the easy options?

Well one way is to the leave scope as it and heavily write them out with Write-Verbose

For Example we could:

FUNCTION Get-SQLDatabaseCount{

        [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)]
        [string]$Server
        )    

$Query = "SELECT COUNT(*) FROM sys.databases"
Write-Verbose "Query Variable ----------------"
Write-Verbose $Query
IF (!(Get-Module -Name sqlps))
    {
        Push-Location
        Import-Module sqlps -DisableNameChecking
        Pop-Location
    }

TRY
    {
        $Result = Invoke-Sqlcmd -ServerInstance $Server -Query 
    }
CATCH
    {
        "Error connecting to server"
    }

$Result
Write-Verbose "Result Variable ----------------"
Write-Verbose $Result
}

This way we can run our Function

Get-SQLDatabaseCount -Server "DEV-SQL01" -Verbose

and we can see our variables as they are in the function..

PowerShellScope05

The other way would be change the level of the scope while testing the function (I think this is a little heavy handed so wont go into it But below is a example from stackoverflow by Kirt Carson

$A="1"
$A
FUNCTION changeA2 () 
    { 
        $global:A="0"
    }
changeA2
$A

Below are some good blogs on scopes if your still interested!
about_Scopes
Scripting Guy!
Jeffrey Snover – Variable Scopes and $PRIVATE:
Master-PowerShell | With Dr. Tobias Weltner | Chapter 12. Managing Scope

Advertisements