Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Nested Switch Statements In PowerShell


By:   |   Last Updated: 2018-04-24   |   Comments   |   Related Tips: More > PowerShell

Problem

In this tip, we値l look at using nested switch statements in PowerShell and how they can be useful in managing logical organization involving multiple steps, especially when we may need to perform further processing from an input. Switch statements provide use in organizing action post input and they can be applied in a variety of applications, including application specific artificial intelligence. We値l look at a few examples where we値l need more details after a first switch statement is met.

Solution

When I receive input and have multiple possibilities for that input, I will compare the switch and if statements in terms of readability in PowerShell. If we have two or three options, an if statement may be readable, but when we have more than five or six, the switch statement begins to be more applicable, though some exceptions may apply (like ranges). Nested switch statements flow in the same manner and can be replaced by if statements, but if we have multiple possibilities within a switch clause, a nested switch may be more useful than an if statement.

Example of If Statements in PowerShell

As a beginning simple demonstration of a nested if, we値l process a character set within a sentence and depending on the special characters from punctuation to spaces, we値l submit an action related to how something is read.

In our first test, we want to extract each character in the sentence. Our second switch statement will look at the next character, so because we値l reference that, we want to look at how to get the next character ($ch2) as well as the current character ($ch) in our sentence. Once we hit the last character, the next character will be null (thus an error will be thrown in PowerShell). For this reason, we値l check if our variable $i plus one is greater than the length of the string since this would be the next character position and if it is, we will not set our second character ($ch2).

[string]$sentence = "Don't put slashes like this, //, in the middle of a sentence.  Please."

for ($i = 1; $i -le $sentence.Length; $i++)
{
    [char]$ch = $sentence.Substring(($i - 1),1)
    if (($i+1) -le $sentence.Length)
    {
        [char]$ch2 = $sentence.Substring(($i),1)
    }
    Write-Host "$ch $ch2"
} 
image

What we see in our output is one character followed by the next character on each loop, such as D and o, then o and n, then n and , etc.

Example of Nested Switch Statements In PowerShell 

 We値l take this work and write a switch statement that will send commands as to how to handle certain characters like spaces, commas and periods. The instructions follow the character indicating what it is and what to do.

A period indicates a firm matter of fact reading, a question indicates the tone shifts up at the end, an exclamation point indicates the person reads with emphasis, a comma indicates a brief pause and a space either indicates the reader continues, or the reader pauses for a longer period than the comma when there is another space following the first space. In the case of a space, we will nest another switch statement to demonstrate how this can be done. This second switch statement will check if the next character is a space also and if it is, it will set the instructions to pause for 2 seconds. Obtaining the next character does not occur unless the first case of a space is hit, meaning that no extra work of the string is performed as long as a comma, period, exclamation point, or question mark are hit.

[string]$sentence = "John!  Don't put slashes like this, //, in the middle of a sentence.  Please."

for ($i = 1; $i -le $sentence.Length; $i++)
{
    [char]$ch = $sentence.Substring(($i - 1),1)

    switch($ch)
    {
        " " 
        { 
            [char]$ch2 = $sentence.Substring(($i),1)
            switch($ch2)
            {
                " " 
                { 
                    $reader = "Long pause"
                    $i = $i+1
                }
                default { "Continue" }
            }
        }
        "," { Write-Host "Brief pause" }
        "." { Write-Host "State firmly" }
        "!" { Write-Host "Exclaim" }
        "?" { Write-Host "Turn tone upward" }
    }
}
exclaim

The above example could be rewritten with the second switch statement being an if statement, as it only has two options, though it provides a simple example of what a nested switch would look like. This helps when we want to nest more options under our second switch statement, such as an example of searching through code to find patterns within T-SQL scripts for optimizing code.

Another Example of Nested Switch Statements In PowerShell  

In the next example, we'll look at a script I sometimes use when I have to optimize hundreds or thousands of T-SQL scripts or objects - rather than look through each, I start with the biggest culprits based on my experience: temp tables, where clauses and joins tend to have the highest payout when optimizing a script - temp tables tend to be overused when a CTE or subquery may work and reduce the writes (sometimes) and where clauses are notorious for being written in ways that don't maximize usage of indexes.

One advantage to using a switch statement here is that we can add another match if we find other code that is problematic, such as cross applies as an example - maybe we want to highlight those as well. The nested switch statement helps when the complexity could increase under each code it found, for an example, we see it catches a where clause in the below script, and we may want to add to this to include a list of functions.

Function Check-TSQL {
    Param(
        [string]$inputline
    )
    Process
    {
        [int]$condition = 0

        switch($inputline)
        {
            ### Some usages of temp tables could be swapped to CTEs or subqueries
            {$_ -match "#"} 
            {
                switch($inputline)
                {
                    {$_ -match "create table #"} { $condition = 1 }
                    {$_ -match "alter table #"} { $condition = 1 }
                    {$_ -match "drop table #"} { $condition = 1 }
                    {$_ -match " into #"} { $condition = 1 }
                    default { $condition = 0 }
                }

                if ($condition -eq 1)
                {
                    Write-Warning "Temp table found."
                }
            }
    
            ### Sargable where usage?
            {$_ -match "where "}  
            { 
                Write-Warning "Where clause found"
                $condition = 1
            }
            
            ### Join columns and filter
            {$_ -match " join "}  
            {
                Write-Warning "Join found"
                $condition = 1  
            }
            
            default { Write-Output "" }
        }

        return $condition
    }
}


Function Loop-Filelines {
    Param(
        [string]$file
    )
    Process
    {
        $readfile = New-Object System.IO.StreamReader($file)

        while (($line = $readfile.ReadLine()) -ne $null)
        {
            if ((Check-TSQL -inputline $line.ToLower()) -eq 1)
            {
                Write-Host "Check file $file"
                break;
            }
        }
    }
}


$allfiles = Get-ChildItem -Path "C:\ETLFiles\" -Filter *.sql

foreach ($onefile in $allfiles)
{
    Loop-Filelines -file $onefile.FullName
}

Summary

As a development practice, I will use switch statements and nested switch statements over if statements when I know that possibilities can grow, or if there are many possibilities in outcome. A six or seven if-else-then statement with that many possibilities may appear less readable than a switch statement. Also, in some cases we may have multiple hits in a switch statement, such as the above filter in code - note that order matters in that I first want to find temp table creation before focusing on where clauses. A T-SQL script may have both.

In my own view, switch statements are cleaner when there are many possibilities. For an example, suppose we also wanted to add a check for cross applies as well in our script. We would simply add the below code to the above script:

{$_ -match "cross apply "}

This only demonstrates an example where we may want further analysis based on input, if we've validated one set of input and this isn't the only way we could write this (we could also use if statements). A nested switch statement may be helpful in logical flows where we have multiple options within options based on input. If we're on teams, we still want to ensure that our code is readable for other members, so we should also consider this in our decision.

Next Steps
  • If statements do well in processing like a stoplight, where the action based on the input is limited with the input being specific (three colors), strict in intent and few options.
  • Switch statements do well in scaling situations, where we may see more options in the future, or may need more information about the input, if the input can change. For an example, when a person is hungry, he may not eat as there could be a degree of hunger, it may depend on the day or his schedule, and it may depend on what he痴 trying to achieve. Switch statements become useful in application specific artificial intelligence.
  • Consider nested switch statements, like nested if statements, when there's additional requirements in a step. If the additional steps involve a few actions, an if statement may suffice (like in our first example, an if statement may be better). If we need to know further information, a nested in switch statement can be very useful.
  • When it comes to performance with if statements or switch statements, in testing them with .NET, we won稚 see much difference unless we have many options, which in that case tends to favor switch statements slightly. Readability will be more important for developers, as the time it may take developers to troubleshoot could be a bigger concern than whether a statement performs slightly better.


Last Updated: 2018-04-24


next webcast button


next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools