Flow Control

As with other programming languages you can use different kind of flow control, such as IF- ELSE, WHILE, etc, which is very useful.

IF – ELSE

The IF-ELSE is very useful. Below we see an example:

declare @customerNumber int
select @customerNumber=CustomerNumber from CUSTOMER where CustomerId=2

if @customerNumber > 1000
print 'The Customer Number is larger than 1000'
else
print 'The Customer Number is not larger than 1000'

BEGIN…END:

If more than one line of code is to be executed within an IF sentence you need to use

BEGIN…END.

Example:

select @customerNumber=CustomerNumber from CUSTOMER where CustomerId=2

if @customerNumber > 1000
begin
print 'The Customer Number is larger than 1000' update CUSTOMER set AreaCode=46 where CustomerId=2
end
else
print 'The Customer Number is not larger than 1000'

WHILE

We can also use WHILE, which is known from other programming languages.

Example:

We are using the CUSTOMER table:

and the following query:

while (select AreaCode from CUSTOMER where CustomerId=1) < 20 
begin
update CUSTOMER set AreaCode = AreaCode + 1
end
select * from CUSTOMER

As you can see the code inside the WHILE loop is executed as long as “AreaCode” for CustomerId=1 is less than 20. For each iteration is the “AreaCode” for that customer incremented with 1.

CASE

The CASE statement evaluates a list of conditions and returns one of multiple possible result expressions.

Example:

We have a “GRADE” table that contains the grades for each student in different courses:

select GradeId, StudentId, CourseId, Grade from GRADE

In the “GRADE” table is the grades stored as numbers, but since the students get grades with the letters A..F (A=5, B=4, C=3, D=2, E=1, F=0), we want to convert the values in the table into letters using a CASE statement:

select GradeId, 
StudentId, 
CourseId, 
case Grade
      when 5 then 'A' 
      when 4 then 'B' 
      when 3 then 'C' 
      when 2 then 'D' 
      when 1 then 'E' 
      when 0 then 'F' 
      else '-'
end	as Grade
from
GRADE

CURSOR

In advances scripts, CURSORs may be very useful. A CURSOR works like an advanced WHILE loop which we use to iterate through the records in one or more tables.

CURSORS are used mainly in stored procedures, triggers, and SQL scripts.

Example:

We use the CUSTOMER table as an example:

We will create a CURSOR that iterate through all the records in the CUSTOMER table and check if the Phone number consists of 8 digits, if not the script will replace the invalid Phone number with the text “Phone number is not valid”.

Here is the SQL Script using a CURSOR:

DECLARE
@CustomerId int, 
@phone varchar(50)


DECLARE db_cursor CURSOR
FOR SELECT CustomerId from CUSTOMER

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @CustomerId

WHILE @@FETCH_STATUS = 0 
BEGIN
      select @phone=Phone from CUSTOMER where CustomerId=@CustomerId 
      if LEN(@phone) < 8
       
      update CUSTOMER set Phone='Phone number is not valid' where CustomerId=@CustomerId

      FETCH NEXT FROM db_cursor INTO @CustomerId
END

CLOSE db_cursor
DEALLOCATE db_cursor

The CUSTOMER table becomes:

Creating and using a CURSOR includes these steps:

• Declare SQL variables to contain the data returned by the Declare one variable for each result set column.
• Associate a SQL cursor with a SELECT statement using the DECLARE CURSOR The DECLARE CURSOR statement also defines the characteristics of the cursor, such as the cursor name and whether the cursor is read-only or forward-only.
• Use the OPEN statement to execute the SELECT statement and populate the
• Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable. Other SQL statements can then reference those variables to access the fetched data values.
• When you are finished with the cursor, use the CLOSE statement. Closing a cursor frees some resources, such as the cursor’s result set and its locks on the current The DEALLOCATE statement completely frees all resources allocated to the cursor, including the cursor name.