Ending My Loop in PowerShell Early

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

I was modifying my PowerShell (PoSh) script to download SQL Saturday files recently to not re-download files. However when I did this, I also realized that I didn’t necessarily want the script to run too long.

One of the challenges of downloading the data is that I don’t know how many events exist. We don’t keep that number handy, and it changes regularly. One of the things I decided to do was run my process in a loop.

While ($i -lt 9999) {

That’s fine, but it’s not a great loop. It runs 9999 times, which isn’t what I want. It works, but it’s an unnecessary use of resources. However I don’t want to break the loop when the file file isn’t found. There have been issues generating a file, like #350, when #351 exists and is there.

I decided to use a shortcut technique I had learned as a kid. I set a variable and then incremented it when I missed a file. When the increment reaches some value, I break the loop.

I decided to use 12 as my number of missed. No good reason, but that’s what I picked. I started by putting a variable outside of the loop.

$missedXML = 0
While ($i -lt 9999) {

Then I increment this variable in the CATCH section of my error handler.

  # if we can't load the file, assume we're done for now. 
  Write-Host "error with  #" $i 

Finally, I set up an IF loop at the bottom of the loop. If I’ve missed 12 times, I break the loop by setting the counter to the last value.

$i = $i + 1
if ($missedXML -ge 12) { 
 $i = 9999 

I tested this with some debugging information and what I found was that when I got to 494, I started missing files. As soon as I hit 12, the loop ended.

Enhancement complete.


This post came about when I started working on the script. I made the modifications from the previous post and decided to also fix the extra loops with this technique.

This post took about 10 minutes to write.


No resources needed here. I’ve got enough PoSh knowledge to handle this task myself.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

3 Responses to Ending My Loop in PowerShell Early

  1. way0utwest says:

    Hmmm, thanks, Brian. I wasn’t aware of break, and it might be clearer. Would you then use a WHILE ($TRUE) of some sort in the loop.

    I’m trying to decide if I think ending the loop based on the loop test is better than BREAK, or assuming a no end to the loop and using BREAK.


    • brianary says:

      I think I’ve seen while($true) more frequently, but providing an expression that eventually exhausts could make sense if there is less certainty about the break condition.


Comments are closed.