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

 

Introduction to Bash Scripting: Iterative Statements Using Loops


By:   |   Last Updated: 2018-12-28   |   Comments (1)   |   Related Tips: More > SQL Server on Linux

Problem

If you are going to be using SQL Server on Linux then it would be helpful to understand how to write bash scripts.  In this tip I will cover the basics of iterative statements which is part of a series of tips about bash scripting.

Solution

With Bash scripts you can create complex solutions. That makes the need of iterative statements a must. For example, if you want to read and process each line of a file, you will have to use an iterative statement. For those who don't know what an iterative statement is, we can think on it as repeatedly executing a portion of code, known as the loop body, until a controlling expression (or condition) reaches a specific value.

Iterative Statements on Bash Scripting

Basically, Bash Scripts allows three different iterative statements:

  • For Loop
  • While Loop
  • Until Loop

Now I will describe each of these statements and differences.

Bash For Loop

This statement is present in all computer languages. Of course, each language has its own implementation, but the basic idea is still the same. The For Loop repeats the execution of a group of statements over a set of items. Those items can be a sequence of numbers or text strings like the name of files in a directory.

The basic syntax of the For Loop in Bash Scripting is as follows:

for [Iteration Variable] in [Iteration Array]; do
   [Iteration Statements]
   ...
done
  • [Iteration Variable]: This is a scalar variable (i.e. not an array) that holds one item at a time of [Iteration Array].
  • [Iteration Array]: This can be either an array variable, a group of numbers or a command that returns a set of items like filenames.
  • [Iteration Statements]: This contains the list of statements to be executed each iteration.

Now I will show you different examples of the For Loop. Let's consider the next case when the For Loop contains a sequence of numbers in the iteration array.

#!/bin/bash

echo "Let's count some numbers!:"

for i  in {1..5}; do
   echo $i
done

echo "Let's count some random numbers!:"

for i  in {1,7,5,9}; do
   echo $i
done

On the previous script there are two For Loop statements. The first, loops through a set composed of the succession of the numbers 1 to 5. The other For Loop, does this with a set of nonconsecutive numbers. Notice that since we are defining a set, we need to use curly brackets.

The next screen capture shows the output of the execution.

Two simple for loop statements.

Let's take into consideration the following script when the iteration array is the execution result of a command, which in this case is the sqlcmd command to list the name of the databases in our instance.

#!/bin/bash

echo "Let's list our databases!:"

for i  in $(sqlcmd -S LINUXSQL -U sa -P 'Pa$$w0rd' -Q 'set nocount on;
 SELECT name FROM sys.databases ' -h -1); do
   echo $i
done

Notice that since we are executing a command to get the iteration array, we need to use the dollar sign and the command enclosed with parenthesis instead of curly brackets like in the previous example.

If you want to use the result of a SQL statement executed with sqlcmd as the source for the iterations, be aware that by default when you execute any SQL statement in sqlcmd the result includes the header and the row count. To avoid that I included "set nocount on" which disables the output of the row count. Also, I used the –h parameter that manages how many rows will be written until the headers are reprinted. This parameter requires that you specify before it the number of rows written until the headers are reprinted, but you can also specify -1 (minus one) which is equivalent to disabling the headers.

Using a for loop statement in conjunction with sqlcmd.

Bash While Loop

The While Loop executes a set of commands as long as control condition remains true. If the control condition is a command, then its execution status must return zero for the iteration statements to execute. In the case where the control condition is an expression like a comparison of a variable with another, then the result of this comparison must return true.

Here is the basic syntax of the While Loop:

while [Control condition]
do
   [Iteration Statements]
done

You can stop the execution of a While Loop by using the "break" statement. This way even when the control condition remains true the while block stops its execution and Bash keeps running the following steps of the script. Another way to stop the execution of a While Loop in case you accidentally make an endless loop, is to send a kill signal to the process (you must use another terminal) or press CTRL+C. The difference between these methods and the "break" statement is that these methods stop the execution of the While Loop as well as the script and the "break" statement only stops the While Loop execution.

Now I will show you a few examples on how to use the While Loop.

In the next example I will show you a countdown from 5 to zero by using an endless loop with a break statement. Notice that I have to add an IF statement in order to check the value of the variable.

!/bin/bash

echo "Let's do a countdown!:"

VAR=5

while [ $VAR ];
do
   echo $VAR
   let VAR=VAR-1
   
   if  [[ $VAR == 0 ]]; then
      break
   fi
done

The next screen capture shows the execution of the previous script.

A while loop with a break condition.

Of course, there is another way that requires less code and is easier to debug, because we get rid of the IF statement and perform the evaluation in the control condition of the While Loop. Take a loop at the next script.

#!/bin/bash

echo "Let's do a countdown!:"

VAR=5

while [ $VAR -gt 0  ];
do
   echo $VAR
   let VAR=VAR-1
done

On the next screen capture you can see that the output of this script is equal to the output of the previous script.

A simple while loop statement.

Bash Until Loop

This iterative statement is almost identical to the While Loop, but it has a crucial difference. In the While Loop the execution of the iteration statements continues as long as the control condition remains true; but in the Until Loop the execution of the iteration statements continues as long as the control condition remains false.

Take a look at the next script when I rewrite the countdown from 5 to zero. Notice that in the While Loop the control condition was testing if the variable was greater than zero; in contrary the Until Loop the control condition is testing if the variable is equal to zero.

#!/bin/bash

echo "Let's do a countdown!:"

VAR=5

until [ $VAR == 0  ];
do
   echo $VAR
   let VAR=VAR-1
done
A countdown implemented with an until loop.
Next Steps


Last Updated: 2018-12-28


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

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.



    



Tuesday, January 01, 2019 - 8:01:01 PM - John Spencer Back To Top

Great Article!!

We used UNIX (HP-UX) in the ‘90s to about 2005. Mainly used the Bash shell.  What you describe in the article does go a long way toward getting ‘Windows people’ up to speed with shell scripting on Linux.  

Glad to see that ‘shell scripting’ is still relevant.   


Learn more about SQL Server tools