What Else?

Introduction

We saw that the primary way to address a condition in code is with the help of the IF operator. As it happens, there could be many other options to consider when trying to solve a problem.

Practical Learning: Introducing Conditions

1. Start Microsoft SQL Server Management Studio and connect
2. On the Object Explorer, right-click the name of the computer and click New Query
3. In the empty document, type:

   -- Stellar Water Point
   /* Types of Accounts
   OTH - Other
   BUS - General Business
   RES - Residential Household
   SGO - Social/Government/Non-Profit Organization
   UUO - Unidentified or Unclassified Type of Organization
   WAT - Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc */
   
   DECLARE @acntNbr             NVarChar(15) = N'9279-570-8394';
   DECLARE @type                nchar(3) = N'RES';
   DECLARE @counterReadingStart numeric  = 5205;
   DECLARE @counterReadingEnd   numeric  = 5222;
   
   DECLARE @consumption int = @counterReadingEnd - @counterReadingStart;
   DECLARE @HCFTotal numeric(10, 2) = @consumption * 748.05;
   DECLARE @gallons numeric = @consumption * 748.05;;
   
   DECLARE @firstTier numeric(10, 2) = @HCFTotal * 41.50 / 10000.00;
   DECLARE @secondTier numeric(10, 2) = @HCFTotal * 32.50 / 10000.00;
   DECLARE @lastTier numeric(10, 2) = @HCFTotal * 26.00 / 10000.00;
   
   DECLARE @waterCharges numeric(10, 2) = @firstTier + @secondTier + @lastTier;
   DECLARE @sewerCharges numeric(10, 2) = @waterCharges * 6.826941 / 100;
   
   DECLARE @environmentCharges numeric(10, 2) = @waterCharges * 0.022724;
   DECLARE @serviceCharges numeric(10, 2) = @waterCharges * 0.145748;
   
   DECLARE @totalCharges numeric(10, 2) = @waterCharges + @sewerCharges + @environmentCharges + @serviceCharges;
   
   DECLARE @localTaxes numeric(10, 2) = @totalCharges * 0.031574;
   DECLARE @stateTaxes numeric(10, 2) = @totalCharges * 0.016724;
   
   DECLARE @amountDue numeric(10, 2) = @totalCharges + @localTaxes + @stateTaxes;
   
   DECLARE @strCounterReadingStart   nvarchar(10);
   DECLARE @strCounterReadingEnd     nvarchar(10);
   DECLARE @strTotalGallonsConsumed  nvarchar(10);
   DECLARE @strHCFTotal              nvarchar(10);
   DECLARE @strGallons               nvarchar(10);
   DECLARE @strFirstTierConsumption  nvarchar(10);
   DECLARE @strSecondTierConsumption nvarchar(10);
   DECLARE @strLastTierConsumption   nvarchar(10);
   DECLARE @strWaterUseCharges       nvarchar(10);
   DECLARE @strSewerCharges          nvarchar(10);
   DECLARE @strEnvironmentCharges    nvarchar(10);
   DECLARE @strServiceCharges        nvarchar(10);
   DECLARE @strTotalCharges          nvarchar(10);
   DECLARE @strLocalTaxes            nvarchar(10);
   DECLARE @strStateTaxes            nvarchar(10);
   DECLARE @strAmountDue             nvarchar(10);
   
   SET @strCounterReadingStart       = @counterReadingStart;
   SET @strCounterReadingEnd         = @counterReadingEnd;
   SET @strTotalGallonsConsumed      = @consumption;
   SET @strHCFTotal                  = @HCFTotal;
   SET @strGallons                   = @gallons;
   SET @strFirstTierConsumption      = @firstTier;
   SET @strSecondTierConsumption     = @secondTier;
   SET @strLastTierConsumption       = @lastTier;
   SET @strWaterUseCharges           = @waterCharges;
   SET @strSewerCharges              = @sewerCharges;
   SET @strEnvironmentCharges        = @environmentCharges;
   SET @strServiceCharges            = @serviceCharges;
   SET @strTotalCharges              = @totalCharges;
   SET @strLocalTaxes                = @localTaxes;
   SET @strStateTaxes                = @stateTaxes;
   SET @strAmountDue                 = @amountDue;
   
   PRINT '======================================================';
   PRINT 'Stellar Water Point - Customer Invoice';
   PRINT '------------------------------------------------------';
   PRINT 'Account Number:             ' + @acntNbr;
   PRINT '======================================================';
   PRINT 'Meter Reading';
   PRINT '------------------------------------------------------';
   PRINT 'Counter Reading Start:      ' + @strCounterReadingStart;
   PRINT 'Counter Reading End:        ' + @strCounterReadingEnd;
   PRINT 'Total Gallons Consumed:     ' + @strTotalGallonsConsumed;
   PRINT 'HCF Total                   ' + @strHCFTotal;
   PRINT 'Gallons                     ' + @strGallons;
   PRINT '======================================================';
   PRINT 'Therms Evaluation';
   PRINT '------------------------------------------------------';
   PRINT 'First Tier Consumption:     ' + @strFirstTierConsumption;
   PRINT 'Second Tier Consumption:    ' + @strSecondTierConsumption;
   PRINT 'Last Tier Consumption:      ' + @strLastTierConsumption;
   PRINT '------------------------------------------------------';
   PRINT 'Water Use Charges:          ' + @strWaterUseCharges;
   PRINT 'Sewer Charges:              ' + @strSewerCharges;
   PRINT '======================================================';
   PRINT 'Bill Values';
   PRINT '------------------------------------------------------';
   PRINT 'Environment Charges:        ' + @strEnvironmentCharges;
   PRINT 'Service Charges:            ' + @strServiceCharges;
   PRINT 'Total Charges:              ' + @strTotalCharges;
   PRINT 'Local Taxes:                ' + @strLocalTaxes;
   PRINT 'State Taxes:                ' + @strStateTaxes;
   PRINT '------------------------------------------------------';
   PRINT 'Amount Due:                 ' + @strAmountDue;
   PRINT '======================================================';

4. To execute, on the main menu, click Query -> Execute:

   ======================================================
   Stellar Water Point - Customer Invoice
   ------------------------------------------------------
   Account Number:             9279-570-8394
   ======================================================
   Meter Reading
   ------------------------------------------------------
   Counter Reading Start:      5205
   Counter Reading End:        5222
   Total Gallons Consumed:     17
   HCF Total                   12716.85
   Gallons                     12717
   ======================================================
   Therms Evaluation
   ------------------------------------------------------
   First Tier Consumption:     52.77
   Second Tier Consumption:    41.33
   Last Tier Consumption:      33.06
   ------------------------------------------------------
   Water Use Charges:          127.16
   Sewer Charges:              8.68
   ======================================================
   Bill Values
   ------------------------------------------------------
   Environment Charges:        2.89
   Service Charges:            18.53
   Total Charges:              157.26
   Local Taxes:                4.97
   State Taxes:                2.63
   ------------------------------------------------------
   Amount Due:                 164.86
   ======================================================

Introduction to IF...ELSE Conditions

If you use an IF condition to perform an operation and if the result is true, we saw that you could execute the statement. Any other result would be ignored. To address an alternative to an IF condition, you can use a keyword named ELSE. The formula to use it is:

IF condition statement_1;
ELSE statement_2;

As you can see, the new section uses the ELSE keyword. That section must come after an IF clause. That section doesn't have a condition, only a statement. If its

As seen with the IF conditional statement, if statement_2 is short, you can write it on the same line with the ELSE condition. Here is an example:

DECLARE @value numeric(6, 2);
DECLARE @choice nvarchar(12) = N'Residential';

IF @choice = N'Residential' SET @value = 16.55;
ELSE SET @value = 19.25;

PRINT 'Account Type: Residential';
PRINT CONCAT('Value:        ', @value);
PRINT '==================================';

This would produce:

Account Type: Residential
Value:        16.55
==================================

To make your code easy to read, you should write statement_2 on its own line.

Practical Learning: Introducing IF...ELSE Condition

1. Change the document as follows:

   -- Stellar Water Point
   /* Types of Accounts
   OTH - Other
   BUS - General Business
   RES - Residential Household
   SGO - Social/Government/Non-Profit Organization
   UUO - Unidentified or Unclassified Type of Organization
   WAT - Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc */
   
   DECLARE @acntNbr             NVarChar(15) = N'9279-570-8394';
   DECLARE @type                nchar(3) = N'RES';
   DECLARE @counterReadingStart numeric  = 5205;
   DECLARE @counterReadingEnd   numeric  = 5222;
   
   DECLARE @consumption  int = @counterReadingEnd - @counterReadingStart;
   DECLARE @HCFTotal     numeric(10, 2) = @consumption * 748.05;
   DECLARE @gallons      numeric = @consumption * 748.05;;
   
   DECLARE @firstTier    numeric(10, 2);
   DECLARE @secondTier   numeric(10, 2);
   DECLARE @lastTier     numeric(10, 2);
   
   DECLARE @sewerCharges numeric(10, 2);
   DECLARE @environmentCharges numeric(10, 2);
   DECLARE @serviceCharges     numeric(10, 2);
   
   IF @type = N'RES'
       SET @firstTier = @HCFTotal * 41.50 / 10000.00;
   ELSE
       SET @firstTier = @HCFTotal * 48.00 / 10000.00;
   
   IF @type = N'RES'
       SET @secondTier = @HCFTotal * 32.50 / 10000.00;
   ELSE
       SET @secondTier = @HCFTotal * 32.00 / 10000.00;
   
   IF @type = N'RES'
       SET @lastTier   = @HCFTotal * 26.00 / 10000.00;
   ELSE
       SET @lastTier   = @HCFTotal * 20.00 / 10000.00;
   
   DECLARE @waterCharges numeric(10, 2) = @firstTier + @secondTier + @lastTier;
   
   IF @type = N'RES'
       SET @sewerCharges = @waterCharges * 6.826941 / 100;
   ELSE
       SET @sewerCharges = @waterCharges * 9.202615 / 100;
   
   IF @type = N'RES'
       SET @environmentCharges = @waterCharges * 0.022724;
   ELSE
       SET @environmentCharges = @waterCharges * 0.221842;
   
   IF @type = N'RES'
       SET @serviceCharges = @waterCharges * 0.145748;
   ELSE
       SET @serviceCharges = @waterCharges * 0.210248;
   
   DECLARE @totalCharges numeric(10, 2) = @waterCharges + @sewerCharges + @environmentCharges + @serviceCharges;
   
   DECLARE @localTaxes numeric(10, 2) = @totalCharges * 0.031574;
   DECLARE @stateTaxes numeric(10, 2) = @totalCharges * 0.016724;
   
   DECLARE @amountDue numeric(10, 2) = @totalCharges + @localTaxes + @stateTaxes;
   
   DECLARE @strCounterReadingStart   nvarchar(10);
   DECLARE @strCounterReadingEnd     nvarchar(10);
   DECLARE @strTotalGallonsConsumed  nvarchar(10);
   DECLARE @strHCFTotal              nvarchar(10);
   DECLARE @strGallons               nvarchar(10);
   DECLARE @strFirstTierConsumption  nvarchar(10);
   DECLARE @strSecondTierConsumption nvarchar(10);
   DECLARE @strLastTierConsumption   nvarchar(10);
   DECLARE @strWaterUseCharges       nvarchar(10);
   DECLARE @strSewerCharges          nvarchar(10);
   DECLARE @strEnvironmentCharges    nvarchar(10);
   DECLARE @strServiceCharges        nvarchar(10);
   DECLARE @strTotalCharges          nvarchar(10);
   DECLARE @strLocalTaxes            nvarchar(10);
   DECLARE @strStateTaxes            nvarchar(10);
   DECLARE @strAmountDue             nvarchar(10);
   
   SET @strCounterReadingStart       = @counterReadingStart;
   SET @strCounterReadingEnd         = @counterReadingEnd;
   SET @strTotalGallonsConsumed      = @consumption;
   SET @strHCFTotal                  = @HCFTotal;
   SET @strGallons                   = @gallons;
   SET @strFirstTierConsumption      = @firstTier;
   SET @strSecondTierConsumption     = @secondTier;
   SET @strLastTierConsumption       = @lastTier;
   SET @strWaterUseCharges           = @waterCharges;
   SET @strSewerCharges              = @sewerCharges;
   SET @strEnvironmentCharges        = @environmentCharges;
   SET @strServiceCharges            = @serviceCharges;
   SET @strTotalCharges              = @totalCharges;
   SET @strLocalTaxes                = @localTaxes;
   SET @strStateTaxes                = @stateTaxes;
   SET @strAmountDue                 = @amountDue;
   
   PRINT '======================================================';
   PRINT 'Stellar Water Point - Customer Invoice';
   PRINT '------------------------------------------------------';
   PRINT 'Account Number:             ' + @acntNbr;
   PRINT '======================================================';
   PRINT 'Meter Reading';
   PRINT '------------------------------------------------------';
   PRINT 'Counter Reading Start:      ' + @strCounterReadingStart;
   PRINT 'Counter Reading End:        ' + @strCounterReadingEnd;
   PRINT 'Total Gallons Consumed:     ' + @strTotalGallonsConsumed;
   PRINT 'HCF Total                   ' + @strHCFTotal;
   PRINT 'Gallons                     ' + @strGallons;
   PRINT '======================================================';
   PRINT 'Therms Evaluation';
   PRINT '------------------------------------------------------';
   PRINT 'First Tier Consumption:     ' + @strFirstTierConsumption;
   PRINT 'Second Tier Consumption:    ' + @strSecondTierConsumption;
   PRINT 'Last Tier Consumption:      ' + @strLastTierConsumption;
   PRINT '------------------------------------------------------';
   PRINT 'Water Use Charges:          ' + @strWaterUseCharges;
   PRINT 'Sewer Charges:              ' + @strSewerCharges;
   PRINT '======================================================';
   PRINT 'Bill Values';
   PRINT '------------------------------------------------------';
   PRINT 'Environment Charges:        ' + @strEnvironmentCharges;
   PRINT 'Service Charges:            ' + @strServiceCharges;
   PRINT 'Total Charges:              ' + @strTotalCharges;
   PRINT 'Local Taxes:                ' + @strLocalTaxes;
   PRINT 'State Taxes:                ' + @strStateTaxes;
   PRINT '------------------------------------------------------';
   PRINT 'Amount Due:                 ' + @strAmountDue;
   PRINT '======================================================';

2. To execute the application, on the main menu, click Query -> Execute:

   ======================================================
   Stellar Water Point - Customer Invoice
   ------------------------------------------------------
   Account Number:             9279-570-8394
   ======================================================
   Meter Reading
   ------------------------------------------------------
   Counter Reading Start:      5205
   Counter Reading End:        5222
   Total Gallons Consumed:     17
   HCF Total                   12716.85
   Gallons                     12717
   ======================================================
   Therms Evaluation
   ------------------------------------------------------
   First Tier Consumption:     52.77
   Second Tier Consumption:    41.33
   Last Tier Consumption:      33.06
   ------------------------------------------------------
   Water Use Charges:          127.16
   Sewer Charges:              8.68
   ======================================================
   Bill Values
   ------------------------------------------------------
   Environment Charges:        2.89
   Service Charges:            18.53
   Total Charges:              157.26
   Local Taxes:                4.97
   State Taxes:                2.63
   ------------------------------------------------------
   Amount Due:                 164.86
   ======================================================

   Notice that the counter reading values are the same, but the calculated results are different

Beginning and Ending an ELSE Clause

As seen with an IF condition, if an ELSE section involves a long or many statements, you must add a body to it. Its body must start with a BEGIN keyword and end with the END keyword. The formula to follow would be:

IF condition
    statement(s);
ELSE
    BEGIN
        statement_1;
        statement_2;
        . . .
        statement_n;
    END

Of course, the IF section can have its own BEGIN...END body too:

IF condition
    BEGIN
        if-statement1;
        if-statement2;
         . . .
        if-statement_n;
    END
ELSE
    BEGIN
        statement_1;
        statement_2;
        . . .
        statement_n;
    END

Variants of an ELSE Conditional Statement

IF...ELSE IF

If you use an IF...ELSE situation, you can process only two statements. In some cases, you may deal with more than two conditions. In this case, you can use an IF...ELSE IF condition. The primary formula to follow is:

IF condition_1 statement_1;
ELSE IF condition_2 statement_2;

If a statement is long or the section includes many statement, you must create a BEGIN...END body for it. This would be done as follows:

IF condition_1
    statement_1;
ELSE IF condition_2
    BEGIN
        statement_1;
        statement_2;
        . . .
       statement_n;
    END

The first condition, condition_1, would first be checked. If condition_1 is true, then statement_1 would execute. If condition_1 is false, then condition_2 would be checked. If condition_2 is true, then the statement(s) in its section would execute. Any other result would be ignored. Here is an example:

DECLARE @employmentStatus NVARCHAR(150) = 'This employment requires C# certification. We will get back to you.';

DECLARE @certification char = 'y';

IF @certification = 'y'
    SET @employmentStatus = 'The employment applicant fullfils the basic requirements.';
ELSE IF @certification = 'Y'
    SET @employmentStatus = 'Employment requirements validated.';

PRINT '==================================================================================================';
PRINT 'Employment Verification';
PRINT '--------------------------------------------------------------------------------------------------';
PRINT 'Candition holds certification: ' + @certification;
PRINT 'Decision Status:               ' + @employmentStatus;
PRINT '==================================================================================================';

This would produce:

==================================================================================================
Employment Verification
--------------------------------------------------------------------------------------------------
Candition holds certification: y
Decision Status:               The employment applicant fullfils the basic requirements.
==================================================================================================

Practical Learning: Using ELSE IF

1. Change the document as follows:

   DECLARE @taxRate numeric(8, 2) = 0.00;
   DECLARE @grossSalary numeric(8, 2) = 582.97;
   
   IF @grossSalary >= 10000
       SET @taxRate = 5.00;
   ELSE IF @grossSalary >= 5000
       SET @taxRate = 4.00;
   ELSE IF @grossSalary >= 1000
       SET @taxRate = 3.00;
   
   DECLARE @taxAmount numeric(8, 2) = @grossSalary * @taxRate / 100.00;
   DECLARE @netPay    numeric(8, 2) = @grossSalary - @taxAmount;
   
   DECLARE @strGrossSalary nvarchar(10);
   DECLARE @strTaxRate     nvarchar(10);
   DECLARE @strTaxAmount   nvarchar(10);
   DECLARE @strNetPay      nvarchar(10);
   
   SET @strGrossSalary = @grossSalary;
   SET @strTaxRate     = @taxRate;
   SET @strTaxAmount   = @taxAmount;
   SET @strNetPay      = @netPay;
   
   PRINT '=====================================';
   PRINT ' - Mississippi - State Income Tax -';
   PRINT '-------------------------------------';
   PRINT 'Gross Salary: ' + @strGrossSalary;
   PRINT 'Tax Rate:     ' + @strTaxRate + '%';
   PRINT 'Tax Amount:   ' + @strTaxAmount;
   PRINT 'Net Pay:      ' + @strNetPay;
   PRINT '=====================================';

2. To execute the application, on the main menu, click Query -> Execute:

   =====================================
    - Mississippi - State Income Tax -
   -------------------------------------
   Gross Salary: 582.97
   Tax Rate:     0.00%
   Tax Amount:   0.00
   Net Pay:      582.97
   =====================================

3. In the code change the @grossSalary value to 3582.97

   DECLARE @taxRate numeric(8, 2)     = 0.00;
   DECLARE @grossSalary numeric(8, 2) = 3582.97;
   
   IF @grossSalary >= 10000
       SET @taxRate = 5.00;
   ELSE IF @grossSalary >= 5000
       SET @taxRate = 4.00;
   ELSE IF @grossSalary >= 1000
       SET @taxRate = 3.00;
   
   DECLARE @taxAmount numeric(8, 2) = @grossSalary * @taxRate / 100.00;
   DECLARE @netPay    numeric(8, 2) = @grossSalary - @taxAmount;
   
   DECLARE @strGrossSalary nvarchar(10);
   DECLARE @strTaxRate     nvarchar(10);
   DECLARE @strTaxAmount   nvarchar(10);
   DECLARE @strNetPay      nvarchar(10);
   
   SET @strGrossSalary = @grossSalary;
   SET @strTaxRate     = @taxRate;
   SET @strTaxAmount   = @taxAmount;
   SET @strNetPay      = @netPay;
   
   PRINT '=====================================';
   PRINT ' - Mississippi - State Income Tax -';
   PRINT '-------------------------------------';
   PRINT 'Gross Salary: ' + @strGrossSalary;
   PRINT 'Tax Rate:     ' + @strTaxRate + '%';
   PRINT 'Tax Amount:   ' + @strTaxAmount;
   PRINT 'Net Pay:      ' + @strNetPay;
   PRINT '=====================================';

4. To execute the application, on the main menu, click Query -> Execute:

   =====================================
    - Mississippi - State Income Tax -
   -------------------------------------
   Gross Salary: 3582.97
   Tax Rate:     3.00%
   Tax Amount:   107.49
   Net Pay:      3475.48
   =====================================

5. In the code, change the @grossSalary value to 7582.97 and press Enter

   DECLARE @taxRate numeric(8, 2) = 0.00;
   
   DECLARE @grossSalary numeric(8, 2) = 7582.97;
   
   DECLARE @strGrossSalary nvarchar(10);
   DECLARE @strTaxRate     nvarchar(10);
   DECLARE @strTaxAmount   nvarchar(10);
   DECLARE @strNetPay      nvarchar(10);
   
   SET @strGrossSalary = @grossSalary;
   SET @strTaxRate     = @taxRate;
   SET @strTaxAmount   = @taxAmount;
   SET @strNetPay      = @netPay;
   
   PRINT '=====================================';
   PRINT ' - Mississippi - State Income Tax -';
   PRINT '-------------------------------------';
   PRINT 'Gross Salary: ' + @strGrossSalary;
   PRINT 'Tax Rate:     ' + @strTaxRate + '%';
   PRINT 'Tax Amount:   ' + @strTaxAmount;
   PRINT 'Net Pay:      ' + @strNetPay;
   PRINT '=====================================';

6. To execute the application, on the main menu, click Query -> Execute:

   =====================================
    - Mississippi - State Income Tax -
   -------------------------------------
   Gross Salary: 7582.97
   Tax Rate:     4.00%
   Tax Amount:   303.32
   Net Pay:      7279.65
   =====================================

7. In the code, change the @grossSalary value to 17582.97 and press Enter

   DECLARE @taxRate numeric(8, 2) = 0.00;
   
   DECLARE @grossSalary numeric(8, 2) = 17582.97;
   
   IF @grossSalary >= 10000
       SET @taxRate = 5.00;
   ELSE IF @grossSalary >= 5000
       SET @taxRate = 4.00;
   ELSE IF @grossSalary >= 1000
       SET @taxRate = 3.00;
   
   DECLARE @taxAmount numeric(8, 2) = @grossSalary * @taxRate / 100.00;
   DECLARE @netPay    numeric(8, 2) = @grossSalary - @taxAmount;
   
   DECLARE @strGrossSalary nvarchar(10);
   DECLARE @strTaxRate     nvarchar(10);
   DECLARE @strTaxAmount   nvarchar(10);
   DECLARE @strNetPay      nvarchar(10);
   
   SET @strGrossSalary = @grossSalary;
   SET @strTaxRate     = @taxRate;
   SET @strTaxAmount   = @taxAmount;
   SET @strNetPay      = @netPay;
   
   PRINT '=====================================';
   PRINT ' - Mississippi - State Income Tax -';
   PRINT '-------------------------------------';
   PRINT 'Gross Salary: ' + @strGrossSalary;
   PRINT 'Tax Rate:     ' + @strTaxRate + '%';
   PRINT 'Tax Amount:   ' + @strTaxAmount;
   PRINT 'Net Pay:      ' + @strNetPay;
   PRINT '=====================================';

8. To execute the application, on the main menu, click Query -> Execute:

   =====================================
    - Mississippi - State Income Tax -
   -------------------------------------
   Gross Salary: 17582.97
   Tax Rate:     5.00%
   Tax Amount:   879.15
   Net Pay:      16703.82
   =====================================

IF... ELSE IF... ELSE

Because there can be other alternatives, you can provide a last ELSE section. The formula to use it is:

IF condition_1
    statement_1;
ELSE IF condition_2
    statement_2;
ELSE
    statement-n;

Here is an example:

DECLARE @employmentStatus NVARCHAR(120);
DECLARE @certification nchar = N'y';

IF @certification = 'y'
    SET @employmentStatus = N'The employment applicant fullfils the basic requirements.';
ELSE IF @certification = 'Y'
    SET @employmentStatus = N'Employment requirements validated.';
ELSE
    SET @employmentStatus = N'This employment requires C# certification. We will get back to you.';

PRINT '========================================================================================';
PRINT 'Employment Verification';
PRINT '----------------------------------------------------------------------------------------';
PRINT 'Candition holds certification: ' + @certification;
PRINT 'Decision Status:               ' + @employmentStatus;
PRINT '========================================================================================';

This would produce:

========================================================================================
Employment Verification
----------------------------------------------------------------------------------------
Candition holds certification: y
Decision Status:               The employment applicant fullfils the basic requirements.
========================================================================================

Here is another version of the code:

DECLARE @employmentStatus NVARCHAR(120);
DECLARE @certification nchar = N'I am not sure';

IF @certification = 'y'
    SET @employmentStatus = N'The employment applicant fullfils the basic requirements.';
ELSE IF @certification = 'Y'
    SET @employmentStatus = N'Employment requirements validated.';
ELSE
    SET @employmentStatus = N'This employment requires C# certification. We will get back to you.';

PRINT '===================================================================================================';
PRINT 'Employment Verification';
PRINT '---------------------------------------------------------------------------------------------------';
PRINT 'Candition holds certification: ' + @certification;
PRINT 'Decision Status:               ' + @employmentStatus;
PRINT '===================================================================================================';

This would produce:

Employment Verification
---------------------------------------------------------------------------------------------------
Candition holds certification: I
Decision Status:               This employment requires C# certification. We will get back to you.
===================================================================================================

If necessary, you can or must add a BEGIN...END body to the ELSE section

IF condition_1
    statement_1;

ELSE IF condition_2
    statement_2;

ELSE
    BEGIN
        statement_1;
        statement_2;
        . . .
       statement_n;
    END

IF...ELSE IF ... ELSE IF and ELSE

Remember that you can start a condition examination with with an IF condition. If there are many possibilities to check, you can add as many ELSE IF section as you judge necessary, then add a last ELSE section.

Practical Learning: Introducing if...else if...else

1. Change the document as follows:

   -- Stellar Water Point
   /* Types of Accounts
   OTH - Other
   BUS - General Business
   RES - Residential Household
   SGO - Social/Government/Non-Profit Organization
   UUO - Unidentified or Unclassified Type of Organization
   WAT - Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc */
   
   DECLARE @acntNbr             NVarChar(15) = N'9279-570-8394';
   DECLARE @type                nchar(3) = N'RES';
   DECLARE @counterReadingStart numeric  = 5205;
   DECLARE @counterReadingEnd   numeric  = 5222;
   
   DECLARE @consumption  int = @counterReadingEnd - @counterReadingStart;
   DECLARE @HCFTotal     numeric(10, 2) = @consumption * 748.05;
   DECLARE @gallons      numeric = @consumption * 748.05;;
   
   DECLARE @firstTier    numeric(10, 2);
   DECLARE @secondTier   numeric(10, 2);
   DECLARE @lastTier     numeric(10, 2);
   
   DECLARE @sewerCharges numeric(10, 2);
   DECLARE @environmentCharges numeric(10, 2);
   DECLARE @serviceCharges     numeric(10, 2);
   
   DECLARE @acntType NVARCHAR(120);
   
   IF @type = N'RES'
       SET @acntType = N'RES - Residential Household';
   ELSE IF @type = N'SGO'
       SET @acntType = N'SGO - Social/Government/Non-Profit Organization';
   ELSE IF @type = N'BUS'
       SET @acntType = N'BUS - General Business';
   ELSE IF @type = N'UUO'
       SET @acntType = N'UUO - Unidentified or Unclassified Type of Organization';
   ELSE IF @type = N'WAT'
       SET @acntType = N'WAT - Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc';
   ELSE
       SET @acntType = N'OTH - Other';
   
   IF @type = N'RES'
       SET @firstTier = @HCFTotal * 41.50 / 10000.00;
   ELSE IF @type = N'SGO'
       SET @firstTier = @HCFTotal * 46.00 / 10000.00;
   ELSE IF @type = N'BUS'
       SET @firstTier = @HCFTotal * 45.00 / 10000.00;
   ELSE IF @type = N'UUO'
       SET @firstTier = @HCFTotal * 25.00 / 10000.00;
   ELSE IF @type = N'WAT'
       SET @firstTier = @HCFTotal * 50.00 / 10000.00;
   ELSE
       SET @firstTier = @HCFTotal * 48.00 / 10000.00;
   
   IF @type = N'RES'
       SET @secondTier = @HCFTotal * 32.50 / 10000.00;
   ELSE IF @type = N'SGO'
       SET @secondTier = @HCFTotal * 50.00 / 10000.00;
   ELSE IF @type = N'BUS'
       SET @secondTier = @HCFTotal * 45.00 / 10000.00;
   ELSE IF @type = N'UUO'
       SET @secondTier = @HCFTotal * 35.00 / 10000.00;
   ELSE IF @type = N'WAT'
       SET @secondTier = @HCFTotal * 40.00 / 10000.00;
   ELSE
       SET @secondTier = @HCFTotal * 32.00 / 10000.00;
   
   IF @type = N'RES'
       SET @lastTier   = @HCFTotal * 26.00 / 10000.00;
   ELSE IF @type = N'SGO'
       SET @lastTier   = @HCFTotal * 4.00 / 10000.00;
   ELSE IF @type = N'BUS'
       SET @lastTier   = @HCFTotal * 25.00 / 10000.00;
   ELSE IF @type = N'UUO'
       SET @lastTier   = @HCFTotal * 40.00 / 10000.00;
   ELSE IF @type = N'WAT'
       SET @lastTier   = @HCFTotal * 10.00 / 10000.00;
   ELSE
       SET @lastTier   = @HCFTotal * 20.00 / 10000.00;
   
   DECLARE @waterCharges numeric(10, 2) = @firstTier + @secondTier + @lastTier;
   
   IF @type = N'RES'
       SET @sewerCharges = @waterCharges * 6.826941 / 100;
   ELSE IF @type = N'SGO'
       SET @sewerCharges = @waterCharges * 4.162522 / 100;
   ELSE IF @type = N'BUS'
       SET @sewerCharges = @waterCharges * 8.315136 / 100;
   ELSE IF @type = N'UUO'
       SET @sewerCharges = @waterCharges * 10.626147 / 100;
   ELSE IF @type = N'WAT'
       SET @sewerCharges = @waterCharges * 12.025135 / 100;
   ELSE
       SET @sewerCharges = @waterCharges * 9.202615 / 100;
   
   IF @type = N'RES'
       SET @environmentCharges = @waterCharges * 0.022724;
   ELSE IF @type = N'SGO'
       SET @environmentCharges = @waterCharges * 0.118242;
   ELSE IF @type = N'BUS'
       SET @environmentCharges = @waterCharges * 0.161369;
   ELSE IF @type = N'UUO'
       SET @environmentCharges = @waterCharges * 0.082477;
   ELSE IF @type = N'WAT'
       SET @environmentCharges = @waterCharges * 0.413574;
   ELSE
       SET @environmentCharges = @waterCharges * 0.221842;
   
   IF @type = N'RES'
       SET @serviceCharges = @waterCharges * 0.145748;
   ELSE IF @type = N'SGO'
       SET @serviceCharges = @waterCharges * 0.102246;
   ELSE IF @type = N'BUS'
       SET @serviceCharges = @waterCharges * 0.242627;
   ELSE IF @type = N'UUO'
       SET @serviceCharges = @waterCharges * 0.186692;
   ELSE IF @type = N'WAT'
       SET @serviceCharges = @waterCharges * 0.412628;
   ELSE
       SET @serviceCharges = @waterCharges * 0.210248;
   
   DECLARE @totalCharges numeric(10, 2) = @waterCharges + @sewerCharges + @environmentCharges + @serviceCharges;
   
   DECLARE @localTaxes numeric(10, 2);
   DECLARE @stateTaxes numeric(10, 2);
   
   IF @type = N'RES'
       SET @localTaxes = @totalCharges * 0.031574;
   ELSE IF @type = N'SGO'
       SET @localTaxes = @totalCharges * 0.035026;
   ELSE IF @type = N'BUS'
       SET @localTaxes = @totalCharges * 0.122517;
   ELSE IF @type = N'UUO'
       SET @localTaxes = @totalCharges * 0.105737;
   ELSE IF @type = N'WAT'
       SET @localTaxes = @totalCharges * 0.153248;
   ELSE
       SET @localTaxes = @totalCharges * 0.125148;
   
   IF @type = N'RES'
       SET @stateTaxes = @totalCharges * 0.016724;
   ELSE IF @type = N'SGO'
       SET @stateTaxes = @totalCharges * 0.008779;
   ELSE IF @type = N'BUS'
       SET @stateTaxes = @totalCharges * 0.042448;
   ELSE IF @type = N'UUO'
       SET @stateTaxes = @totalCharges * 0.067958;
   ELSE IF @type = N'WAT'
       SET @stateTaxes = @totalCharges * 0.081622;
   ELSE
       SET @stateTaxes = @totalCharges * 0.013746;
   
   DECLARE @amountDue numeric(10, 2) = @totalCharges + @localTaxes + @stateTaxes;
   
   DECLARE @lateAmountDue numeric(10, 2);
   
   IF @type = N'RES'
       SET @lateAmountDue = @amountDue + 8.95;
   ELSE IF @type = N'SGO'
       SET @lateAmountDue = @amountDue + (@amountDue / 4.575);
   ELSE IF @type = N'BUS'
       SET @lateAmountDue = @amountDue + (@amountDue / 12.315);
   ELSE IF @type = N'UUO'
       SET @lateAmountDue = @amountDue + (@amountDue / 7.425);
   ELSE IF @type = N'WAT'
       SET @lateAmountDue = @amountDue + (@amountDue / 15.225);
   ELSE
       SET @lateAmountDue = @amountDue + (@amountDue / 6.735);
   
   DECLARE @strCounterReadingStart   nvarchar(10);
   DECLARE @strCounterReadingEnd     nvarchar(10);
   DECLARE @strTotalGallonsConsumed  nvarchar(10);
   DECLARE @strHCFTotal              nvarchar(10);
   DECLARE @strGallons               nvarchar(10);
   DECLARE @strFirstTierConsumption  nvarchar(10);
   DECLARE @strSecondTierConsumption nvarchar(10);
   DECLARE @strLastTierConsumption   nvarchar(10);
   DECLARE @strWaterUseCharges       nvarchar(10);
   DECLARE @strSewerCharges          nvarchar(10);
   DECLARE @strEnvironmentCharges    nvarchar(10);
   DECLARE @strServiceCharges        nvarchar(10);
   DECLARE @strTotalCharges          nvarchar(10);
   DECLARE @strLocalTaxes            nvarchar(10);
   DECLARE @strStateTaxes            nvarchar(10);
   DECLARE @strAmountDue             nvarchar(10);
   DECLARE @strLateAmountDue         nvarchar(10);
   
   SET @strCounterReadingStart       = @counterReadingStart;
   SET @strCounterReadingEnd         = @counterReadingEnd;
   SET @strTotalGallonsConsumed      = @consumption;
   SET @strHCFTotal                  = @HCFTotal;
   SET @strGallons                   = @gallons;
   SET @strFirstTierConsumption      = @firstTier;
   SET @strSecondTierConsumption     = @secondTier;
   SET @strLastTierConsumption       = @lastTier;
   SET @strWaterUseCharges           = @waterCharges;
   SET @strSewerCharges              = @sewerCharges;
   SET @strEnvironmentCharges        = @environmentCharges;
   SET @strServiceCharges            = @serviceCharges;
   SET @strTotalCharges              = @totalCharges;
   SET @strLocalTaxes                = @localTaxes;
   SET @strStateTaxes                = @stateTaxes;
   SET @strAmountDue                 = @amountDue;
   SET @strLateAmountDue             = @lateAmountDue;
   
   PRINT '======================================================';
   PRINT 'Stellar Water Point - Customer Invoice';
   PRINT '------------------------------------------------------';
   PRINT 'Account Number:             ' + @acntNbr;
   PRINT 'Account Type:               ' + @acntType;
   PRINT '======================================================';
   PRINT 'Meter Reading';
   PRINT '------------------------------------------------------';
   PRINT 'Counter Reading Start:      ' + @strCounterReadingStart;
   PRINT 'Counter Reading End:        ' + @strCounterReadingEnd;
   PRINT 'Total Gallons Consumed:     ' + @strTotalGallonsConsumed;
   PRINT 'HCF Total                   ' + @strHCFTotal;
   PRINT 'Gallons                     ' + @strGallons;
   PRINT '======================================================';
   PRINT 'Therms Evaluation';
   PRINT '------------------------------------------------------';
   PRINT 'First Tier Consumption:     ' + @strFirstTierConsumption;
   PRINT 'Second Tier Consumption:    ' + @strSecondTierConsumption;
   PRINT 'Last Tier Consumption:      ' + @strLastTierConsumption;
   PRINT '------------------------------------------------------';
   PRINT 'Water Use Charges:          ' + @strWaterUseCharges;
   PRINT 'Sewer Charges:              ' + @strSewerCharges;
   PRINT '======================================================';
   PRINT 'Bill Values';
   PRINT '------------------------------------------------------';
   PRINT 'Environment Charges:        ' + @strEnvironmentCharges;
   PRINT 'Service Charges:            ' + @strServiceCharges;
   PRINT 'Total Charges:              ' + @strTotalCharges;
   PRINT 'Local Taxes:                ' + @strLocalTaxes;
   PRINT 'State Taxes:                ' + @strStateTaxes;
   PRINT '------------------------------------------------------';
   PRINT 'Amount Due:                 ' + @strAmountDue;
   PRINT 'Late Amount Due:            ' + @strLateAmountDue;
   PRINT '======================================================';

2. To execute, on the main menu, click Query -> Execute:

   ===================================================================================================
   Stellar Water Point - Customer Invoice
   ---------------------------------------------------------------------------------------------------
   Account Number:             9279-570-8394
   Account Type:               RES - Residential Household
   ===================================================================================================
   Meter Reading
   ---------------------------------------------------------------------------------------------------
   Counter Reading Start:      5205
   Counter Reading End:        5222
   Total Gallons Consumed:     17
   HCF Total                   12716.85
   Gallons                     12717
   ===================================================================================================
   Therms Evaluation
   ---------------------------------------------------------------------------------------------------
   First Tier Consumption:     52.77
   Second Tier Consumption:    41.33
   Last Tier Consumption:      33.06
   ---------------------------------------------------------------------------------------------------
   Water Use Charges:          127.16
   Sewer Charges:              8.68
   ===================================================================================================
   Bill Values
   ---------------------------------------------------------------------------------------------------
   Environment Charges:        2.89
   Service Charges:            18.53
   Total Charges:              157.26
   Local Taxes:                4.97
   State Taxes:                2.63
   ---------------------------------------------------------------------------------------------------
   Amount Due:                 164.86
   Late Amount Due:            173.81
   ===================================================================================================

3. In the code, change the value of the @acntNbr variable to 4820-375-2842
4. In the code, change the @type value to SGO
5. To execute, on the main menu, click Query -> Execute:

   ===================================================================================================
   Stellar Water Point - Customer Invoice
   ---------------------------------------------------------------------------------------------------
   Account Number:             4820-375-2842
   Account Type:               SGO - Social/Government/Non-Profit Organization
   ===================================================================================================
   Meter Reading
   ---------------------------------------------------------------------------------------------------
   Counter Reading Start:      5205
   Counter Reading End:        5222
   Total Gallons Consumed:     17
   HCF Total                   12716.85
   Gallons                     12717
   ===================================================================================================
   Therms Evaluation
   ---------------------------------------------------------------------------------------------------
   First Tier Consumption:     58.50
   Second Tier Consumption:    63.58
   Last Tier Consumption:      5.09
   ---------------------------------------------------------------------------------------------------
   Water Use Charges:          127.17
   Sewer Charges:              5.29
   ===================================================================================================
   Bill Values
   ---------------------------------------------------------------------------------------------------
   Environment Charges:        15.04
   Service Charges:            13.00
   Total Charges:              160.50
   Local Taxes:                5.62
   State Taxes:                1.41
   ---------------------------------------------------------------------------------------------------
   Amount Due:                 167.53
   Late Amount Due:            204.15
   ===================================================================================================

6. In the code, change the value of the @acntNbr variable to 9618-579-2577
7. In the code, change the @type value to UUO
8. To execute, on the main menu, click Query -> Execute:

   ===================================================================================================
   Stellar Water Point - Customer Invoice
   ---------------------------------------------------------------------------------------------------
   Account Number:             9618-579-2577
   Account Type:               UUO - Unidentified or Unclassified Type of Organization
   ===================================================================================================
   Meter Reading
   ---------------------------------------------------------------------------------------------------
   Counter Reading Start:      5205
   Counter Reading End:        5222
   Total Gallons Consumed:     17
   HCF Total                   12716.85
   Gallons                     12717
   ===================================================================================================
   Therms Evaluation
   ---------------------------------------------------------------------------------------------------
   First Tier Consumption:     31.79
   Second Tier Consumption:    44.51
   Last Tier Consumption:      50.87
   ---------------------------------------------------------------------------------------------------
   Water Use Charges:          127.17
   Sewer Charges:              13.51
   ===================================================================================================
   Bill Values
   ---------------------------------------------------------------------------------------------------
   Environment Charges:        10.49
   Service Charges:            23.74
   Total Charges:              174.91
   Local Taxes:                18.49
   State Taxes:                11.89
   ---------------------------------------------------------------------------------------------------
   Amount Due:                 205.29
   Late Amount Due:            232.94
   ===================================================================================================

9. Change the value of the @acntNbr variable to 7028-405-9381 and press Enter
10. Change the value of the @type variable to WAT and press Enter
11. To execute, on the main menu, click Query -> Execute:

    ===================================================================================================
    Stellar Water Point - Customer Invoice
    ---------------------------------------------------------------------------------------------------
    Account Number:             7028-405-9381
    Account Type:               WAT - Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc
    ===================================================================================================
    Meter Reading
    ---------------------------------------------------------------------------------------------------
    Counter Reading Start:      5205
    Counter Reading End:        5222
    Total Gallons Consumed:     17
    HCF Total                   12716.85
    Gallons                     12717
    ===================================================================================================
    Therms Evaluation
    ---------------------------------------------------------------------------------------------------
    First Tier Consumption:     63.58
    Second Tier Consumption:    50.87
    Last Tier Consumption:      12.72
    ---------------------------------------------------------------------------------------------------
    Water Use Charges:          127.17
    Sewer Charges:              15.29
    ===================================================================================================
    Bill Values
    ---------------------------------------------------------------------------------------------------
    Environment Charges:        52.59
    Service Charges:            52.47
    Total Charges:              247.52
    Local Taxes:                37.93
    State Taxes:                20.20
    ---------------------------------------------------------------------------------------------------
    Amount Due:                 305.65
    Late Amount Due:            325.73
    ===================================================================================================

12. Close Microsoft SQL Server
13. If asked whether you want to save the file, click No

Previous	Copyright © 2007-2026, FunctionX	Last Update: Sunday 06 April 2025, 14:35	Next