Home

SQL Expressions: Conditional Statements

   

Introduction

A conditional statement is an expression you formulate to evaluate it. Most of the time,  the statement is written so that, when evaluated, it can produce a result of true or false, then, depending on the outcome, you can take action. A condition is usually written as simple as possible to make it clear to you and the SQL interpreter. Although the interpreter never gets confused, if you create a difficult statement, you may receive an unpredictable result.

In the next few sections, we will review the keywords and formulas that Transact-SQL provides to help you formulate clear expressions. Expressions usually start with a keyword, followed by the expression itself. After the expression, you can tell the interpreter what to do. The statement may appear as follows:

Keyword Expression
	Statement

With the above formula, we will always let you know what keyword you can use, why, and when.

BEGIN...END

After the expression, you can write the statement in one line. This is the statement that would be executed if/when the Expression of our formula is satisfied. In most cases, you will need more than one line of code to specify the Statement. As it happens, the interpreter considers whatever comes after the Statement as a unit but only the line immediately after the Expression. To indicate that your Statement covers more than one line, start it with the BEGIN keyword. Then you must use the END keyword to indicate where the Statement ends. In this case, the formula of a conditional statement would appear as follows:

Keyword Expression
BEGIN
	Statement Line 1
	Statement Line 2
	
	Statement Line n
END

You can still use the BEGIN...END combination even if your Statement covers only one line:

Keyword Expression
BEGIN
	Statement
END

Using the BEGIN...END combination makes your code easier to read because it clearly indicates the start and end of the Statement.

IF a Condition is True

Probably the primary comparison you can perform on a statement is to find out whether it is true. This operation is performed using an IF statement in Transact-SQL. Its basic formula is:

IF Condition
	Statement

When creating an IF statement, first make sure you provide a Condition expression that can be evaluated to produce true or false. To create this Condition, you can use variables and the logical comparison operator reviewed above.

When the interpreter executes this statement, it first examines the Condition to evaluate it to a true result. If the Condition produces true, then the interpreter executes the Statement. Here is an example:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Exercise5
{
    public partial class Form1 : Form
    {
	public Exercise()
    	{
	    InitializeComponent();
	}

	private void btnConditions_Click(object sender, EventArgs e)
	{
	    SqlConnection connection =
		new SqlConnection("Data Source=(local);" +
				  "Database='Exercise4';" +
				  "Integrated Security=yes;");
	    SqlCommand command =
 		 new SqlCommand("DECLARE @DateHired As DateTime, " +
				"@CurrentDate As DateTime " +
				"SET @DateHired = '1996/10/04' " +
				"SET @CurrentDate = '2007/04/11' " +
				"IF @DateHired < @CurrentDate " +
		"SELECT N'You have the experience required for a new promotion in this job'",
				connection);
				connection.Open();
	    SqlDataReader rdr = command.ExecuteReader();

	    while (rdr.Read())
	    {
		MessageBox.Show(rdr[0].ToString());
   	    }

	    rdr.Close();
	    connection.Close();
	}
    }
}

This would produce:

IF

 

IF...ELSE

The IF condition we used above is appropriate when you only need to know if an expression is true. There is nothing to do in other alternatives. Consider the following code:

private void btnConditions_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise4';" +
			  "Integrated Security=yes;");
    SqlCommand command =
	 new SqlCommand("DECLARE @DateHired As DateTime," +
			" @CurrentDate As DateTime;" +
			"SET @DateHired = '1996/10/04'" +
			"SET @CurrentDate = '2007/04/16';" +
			"IF @DateHired > @CurrentDate" +
	" PRINT 'You have the experience required for a new promotion';",
			connection);
    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
    {
	MessageBox.Show(rdr[0].ToString());
    }

    rdr.Close();
    connection.Close();
} 

In case the expression to examine produces a false result, there is nothing to do. Sometimes this will happen.

CASE...WHEN...THEN

The CASE keyword is used as a conditional operator that considers a value, examines it, and acts on an option depending on the value. The formula of the CASE statement is:

CASE Expression
	WHEN Value1 THEN Result
	WHEN Value2 THEN Result

	WHEN Value_n THEN Result
END

In the following example, a letter that represents a student is provided. If the letter is m or M, a string is created as Male. If the value is provided as f or F, a string is created as Female:

private void btnConditions_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise4';" +
			  "Integrated Security=yes;");
    SqlCommand command =
	 new SqlCommand("DECLARE @CharGender Char(1)," +
			"@Gender Varchar(20);" +
			"SET @CharGender = 'F';" +
			"SET @Gender = " +
			"CASE @CharGender" +
			"    WHEN 'm' THEN 'Male'" +
			"    WHEN 'M' THEN 'Male'" +
			"    WHEN 'f' THEN 'Female'" +
			"    WHEN 'F' THEN 'Female'" +
			"END;" +
			"SELECT N'Student Gender: ' + @Gender;",
			connection);
    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
    {
	MessageBox.Show(rdr[0].ToString());
    }

    rdr.Close();
    connection.Close();
} 

Here is the result of executing it:

CASE

CASE...WHEN...THEN...ELSE

In most cases, you may know the only types of values that would be submitted to a CASE statement. In some other cases, an unpredictable value may be submitted. If you anticipate a value other than those you are aware of, the CASE statement provides a "fit-all' alternative by using the last statement as ELSE. In this case, the formula of the CASE statement would be:

CASE Expression
	WHEN Value1 THEN Result
	WHEN Value2 THEN Result
	WHEN Value_n THEN Result
	
	ELSE Alternative
END

The ELSE statement, as the last, is used when none of the values of the WHEN statements fits. Here is an example:

private void btnConditions_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise4';" +
			  "Integrated Security=yes;");
    SqlCommand command =
	 new SqlCommand("DECLARE @CharGender Char(1)," +
			"@Gender Varchar(20);" +
			"SET @CharGender = 'g';" +
			"SET @Gender = " +
			"CASE @CharGender" +
			"    WHEN 'm' THEN 'Male'" +
			"    WHEN 'M' THEN 'Male'" +
			"    WHEN 'f' THEN 'Female'" +
			"    WHEN 'F' THEN 'Female'" +
			"ELSE 'Unknown'" +
			"END;" +
			"SELECT N'Student Gender: ' + @Gender;",
			connection);
    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
    {
	MessageBox.Show(rdr[0].ToString());
    }

    rdr.Close();
    connection.Close();
}

This would produce:

CASE...WHEN...THEN...ELSE

If you do not produce an ELSE statement but a value not addressed by any of the WHEN statements is produced, the result would be NULL. Here is an example:

private void btnConditions_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise4';" +
			  "Integrated Security=yes;");
    SqlCommand command =
	 new SqlCommand("DECLARE @CharGender Char(1)," +
			"@Gender Varchar(20);" +
			"SET @CharGender = 'Q';" +
			"SET @Gender = " +
			"CASE @CharGender" +
			"    WHEN 'm' THEN 'Male'" +
			"    WHEN 'M' THEN 'Male'" +
			"    WHEN 'f' THEN 'Female'" +
			"    WHEN 'F' THEN 'Female'" +
			"ELSE 'Unknown'" +
			"END;" +
			"SELECT N'Student Gender: ' + @Gender;",
			connection);
    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
    {
	MessageBox.Show(rdr[0].ToString());
    }

    rdr.Close();
    connection.Close();
}

This would produce:

CASE...WHEN...THEN...ELSE

This means that it is a valuable safeguard to always include an ELSE sub-statement in a CASE statement.

WHILE

As done in C#, to examine a condition and evaluate it before taking action in SQL, you can use the WHILE operator. The basic formula of this statement is:

WHILE Expression 
    Statement

When implementing this statement, first provide an Expression after the WHILE keyword. The Expression must produce a true or a false result. If the Expression is true, then the interpreter executes the Statement. After executing the Statement, the Expression is checked again. AS LONG AS the Expression is true, it will keep executing the Statement. When or once the Expression becomes false, it stops executing the Statement. This scenario can be illustrated as follows:

WHILE

Here is an example:

DECLARE @Number As int

WHILE @Number < 5
	SELECT @Number AS Number
GO

To effectively execute a while condition, you should make sure you provide a mechanism for the interpreter to get a referenced value for the condition, variable, or expression being checked. This is sometimes in the form of a variable being initialized although it could be some other expression. Such a while condition could be illustrated as follows:

WHILE

This time, the statement would be implemented as follows:

private void btnConditions_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise4';" +
			  "Integrated Security=yes;");
    SqlCommand command =
	 new SqlCommand("DECLARE @Number As int;" +
			"SET @Number = 1;" +
			"WHILE @Number < 5" +
			"    BEGIN" +
			"        SELECT @Number AS Number;" +
			"        SET @Number = @Number + 1;" +
			"    END;",
			connection);
    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
	MessageBox.Show(rdr[0].ToString());

    rdr.Close();
    connection.Close();
}  
 

Published on Monday 27 December 2007

 

Previous Copyright © 2007 FunctionX, Inc. Next