Database Reference
In-Depth Information
Figure 7.1
Declare and use variable.
7. 2 .1 Va ria b l e s
A variable is used to hold data values during the process of SQL program execution. After a vari-
able is declared, you can assign a value to it. he value in a variable can be dynamically modi-
ied. Variables can be used in an SQL statement. In a procedure, a variable can be used to hold a
returned value from a function call or used in a low-control structure. he use of variables will be
illustrated throughout this chapter. Let us start with a simple example where a variable is created
and assigned a student id value. After the variable is assigned a value, the example shows how to
use the variable in a SELECT statement. he @ sign indicates that a name is used for a variable
(Figure 7.1).
As shown in Figure 7.1, the variable @Sid is declared as an INT type, which is a data type pro-
vided by Transact-SQL. Besides built-in data types, Transact-SQL allows users to deine their own
data types. You can create complicated user-deined data types with struct in C# or with Structure
in VB .NET and use these data types in SQL statements.
7.2.2 Flow-Control Structures
As mentioned before, multiple SQL statements are included in an SQL procedure. Usually, the
order of execution of multiple SQL statements may not be sequential, meaning that the order of
execution is the same as the order in which the code appears in the program. Sometimes, you
may need to run a certain part of the code repeatedly. Sometimes, you may execute a certain
part of the code based on a given condition. To control the code execution order, Transact-SQL
provides several low-control structures. he commonly used low-control keywords are shown
in Table 7.1.
To illustrate the use of the low-control keywords, let us consider an example, which includes a
WHILE loop and an IF. . .ELSE structure. In this example, we will move the classes from Monday
to Tuesday. hat is, the class 1000 that teaches the course ISC2301 and the class 1001 that teaches
ISC3311 will be moved to Tuesday. Based on the Information Systems department's regulation, no
two classes should teach the same course on the same day. herefore, we need to check Tuesday's
class schedule to see if there are classes that teach the same course. If so, move one of the classes
that teach the same course to Wednesday. Repeat the same process for Wednesday and hursday to
make sure that no two classes teach the same course on the same day. A WHILE loop will be used
to loop through each weekday. Inside each loop, if it is Monday, we will simply move Monday's
classes to Tuesday. Otherwise, we will check if there are two classes that teach the same course on
