By: Daniel Farina | Updated: 2018-12-20 | Comments (2) | Related: > SQL Server on Linux
Problem
Since SQL Server now runs on Linux, it would be helpful to understand how to write Linux scripts to help administer SQL Server, similar to what you do with Windows batch scripts. In this tip we cover how to use conditional logic in the scripts.
Solution
Every programming and scripting language has conditional statements. If there is something in common between Transact SQL, Assembler and batch files it is the presence of conditional statements. I assume that all readers know what conditional statements are, but if not here is a brief description.
A conditional statement, usually referred as an if..then statement, is a feature of programming languages that performs an evaluation of one or more conditions and according to the evaluation follows a specific execution flow. The conditions are evaluated as true or false. In other words, If conditions are true then some action(s) is (are) executed.
The If..Then Construct
This construct allows us to take a different course of action when some conditions are met in our script. The basic syntax of the If..Then construct is as follows:
if [[ conditional expression1 ]] then expression1 statement1 expression1 statement2 ... elif [[ conditional expression2 ]] then expression2 statement1 expression2 statement2 ... else expression3 statement1 expression3 statement ... fi
The double brackets hold a conditional expression that is tested. If the expression tested is true (or zero which is the numeric value of true) then expression1 is executed. But if the conditional expression is evaluated to false (or one) then expression1 is overlooked and the execution follows to the next elif (else if) evaluation; or in the last instance (i.e. if none of the conditions were met) runs the statements in the else clause.
A word of advice, you may see scripts that uses single brackets "[" instead of double brackets "[[". The difference between using single or double brackets is that double brackets are a bash construct that is not POSIX compatible. The single bracket is POSIX compatible, but it is prone to mistakes. Here is a link to a page that explains this differences with further details in case you are curious http://mywiki.wooledge.org/BashFAQ/031.
But things are not that easy when you face it for the first time. For example, consider the following code which is a perfectly valid script and sample.sh is the file name of the script:
#!/bin/bash if [[ -e sample.sh ]] then echo "File sample.sh Exists" else echo "File sample.sh doesn't Exists" fi
If you never worked with a bash script, or even if you have worked as a programmer I am sure that the conditional expression of the previous script "-e sample.sh ” is something disturbing. In order to understand this; think that the content between brackets is an expression to be tested, in this case the existence of a file named sample.sh. There is a command on Linux named "test”. Long story short, Bash treats the content between brackets as the arguments of the test command (if you want a more detailed explanation which is far from the scope of this tip, you should take a look at the previous link which I am repeating here http://mywiki.wooledge.org/BashFAQ/031).
In other words, "[[ -e sample.sh ]]” is like executing "test -e sample.sh”. But if you run the test command you won't see any result unless you run the "echo $?” command which is used to display the exit status of the command previously executed.
Let's consider the following example when I run the test command with a simple numeric comparison.
test 1 = 1 echo $? test 1 = 2 echo $?
On the next screen capture you will see the output of the execution of the previous code.
Now let's change the "test” to double square brackets and see what happens:
[[ 1 = 1 ]] echo $? [[ 1 = 2 ]] echo $?
As you can see on the previous screen capture the results are identical.
Test Expressions
On the next table I included the test expressions that you can use with IF statements. Notice that there is a blank space after the opening double brackets and another one before the closing double brackets.
| Test Expression | Description |
|---|---|
| [[ -a Filename ]] | Checks if Filename exists and if so returns true. |
| [[ –e Filename ]] | Same as above. |
| [[ -b Filename ]] | Returns true if the file exists and is a block device. |
| [[ -c Filename ]] | Returns true if the file exists and is a character device like a serial port. |
| [[ -d DirectoryName ]] | Returns true if the directory exists. |
| [[ -h Filename ]] | Returns true if the file exists and is a symbolic link. |
| [[ -p Filename ]] | Returns true if the file exists and is a named pipe. |
| [[ -r Filename ]] | Returns true if the file exists and is readable (i.e. if you can read it). |
| [[ -s Filename ]] | Returns true if the file exists and its size is bigger than zero. |
| [[ -w Filename ]] | Returns true if the file exists and is writable. |
| [[ -x Filename ]] | Returns true if the file exists and is executable. |
| [[ Filename1 -nt Filename2 ]] | Returns true if Filename1 is newer than Filename2; or if Filename1 exists and its counterpart doesn't exist. |
| [[ Filename1 -ot Filename2 ]] | Returns true if Filename1 is older than Filename2; or if Filename2 exists and Filename1 doesn't exist. |
| [[-o OptionName ]] | Returns true if the shell option "OptionName" is enabled. |
| [[ -z String ]] | Returns true if the length of String is zero. |
| [[ -n String ]] | Returns true if the length of String is greater than zero. |
| [[ String ]] | Same as above. |
| [[ String1 == String2 ]] | Returns true if both strings are equal. |
| [[ String1 = String2 ]] | Same as above. |
| [[ String1 != String2 ]] | Returns true if both strings are not equal. |
| [[ String1 < String2 ]] | Returns true if String1 sorts before String2 lexicographically in the current locale. |
| [[ String1 > String2 ]] | Returns true if String1 sorts after String2 lexicographically in the current locale. |
| [[ Number1 -gt Number2 ]] | Returns true if Number1 is greater than Number2. |
| [[ Number1 -lt Number2 ]] | Returns true if Number1 is less than Number2. |
| [[ Number1 -ge Number2 ]] | Returns true if Number1 is greater than Number2 or both numbers are equal. |
| [[ Number1 -le Number2 ]] | Returns true if Number1 is less than Number2 or both numbers are equal. |
| [[ Number1 -eq Number2 ]] | Returns true if Number1 is equal to Number2. |
| [[ Number1 -ne Number2 ]] | Returns true if Number1 is different to Number2 |
| [[ !Expression ]] | Returns true if Expression is false. |
| [[ (Expression) ]] | You can use parenthesis to override operator precedence. Just like in algebra. |
| [[ Expression1 && Expression2 ]] | Returns true if both Expression1 and Expression2 are true. It's a logical AND. |
| [[ Expression1 || Expression2 ]] | Returns true if any of Expression1 or Expression2 are true. It's a logical OR. |
If Statement Examples
In the next example, we will test a given file and return a message telling if the file is a block device, a character device or a normal file.
#!/bin/bash read -p "Enter a file name: " VAR if [[ -b $VAR ]] then echo $VAR " is a block device" elif [[ -c $VAR ]] then echo $VAR " is a character device" else echo $VAR " is a normal file" fi
The next screen capture shows the execution of the previous script entering a character device (/dev/tty, the console); a block device (/dev/sda, the hard disk) and a regular file.
The Case Statement
Bash programming also allow us to use Case statements. Unlike the if..then statement, the Case statement in Bash programming is very similar to the case statement in other languages, including Transact-SQL.
The syntax of the Case statement is as follows:
case <Expression> in
case_1|case_2) Command_1;
Command_n;;
case_3) Command_1;
Command_n;;
case_n) Command_1;
Command_n;;
*) Command_1;
Command_n;;
esac
The Case statement executes one or several groups of statements, depending on the evaluation of a given <Expression> over a set of cases (case_1..case_n). The expression can be an integer or a string. As a consequence, case_1..case_n can also be either an integer or a string. If you need to run a group of statements for different case values of a given <Expression>, you can separate each value with a vertical bar – i.e "case_1|case_2)”-. Additionally you can include a group of statements to be executed when <Expression> does not match with any of the evaluation cases by using an asterisk – i.e. "*)”-.
You can include as many statements as you want in each case group; you should separate each statement with a semicolon (;). Also you must separate each case block with a double semicolon (;;). If you only use one semicolon (;) then the execution will continue into the next case groups until a double semicolon is found (;;).
Case Examples
The next script shows an example on how to use the case statement with string patterns.
#!/bin/bash echo "What's your favorite sport?:" echo "Soccer" echo "Football" echo "Volleyball" read -p "Write your choice: " VAR case $VAR in Soccer) echo "You chose Soccer";; Football) echo "You chose Football";; Volleyball) echo "You chose Volleyball";; esac
On the next screen capture you can see the result of the previous script execution.
The next script is equivalent to the previous one with the sole difference that it uses a numeric pattern instead of a string.
#!/bin/bash echo "What's your favorite sport?:" echo "1 - Soccer" echo "2 - Football" echo "3 - Volleyball" read -p "Write your choice: " VAR case $VAR in 1) echo "You chose Soccer";; 2) echo "You chose Football";; 3) echo "You chose Volleyball";; esac
On the next screen capture you can see the result of the previous script execution.
Let's see an example of a case statement in which the case expression evaluation for two different case values executes the same section of code.
#!/bin/bash echo "Pick the sport that you want to know which ball to use:" echo "1 - Soccer" echo "2 - Football" echo "3 - Volleyball" read -p "Write your choice: " VAR case $VAR in 1|3) echo "This sport uses a rounded ball";; 2) echo "This sport uses an oval ball";; esac
On the next screen capture you can see the output of its execution.
Next Steps
- Stay tuned for the next tips in this series.
- Are you new on SQL Server running on Linux? You must read this tip: Getting Started with SQL Server on Linux.
- Are you a SQL Server DBA new to Linux? Here you will find 7 Things Every SQL Server DBA Should Know About Linux.
- In this tip you will find the Top 10 Linux Commands for SQL Server DBAs.
- For more reading, take a look at the SQL Server on Linux Tips Category.
About the author
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-12-20
