Procedural Extensions, IF & WHILE Statements

The preceding chapters introduced Transact-SQL statements that belong to the data definition language and the data manipulation language. Most of these statements can be grouped together to build a batch. As previously mentioned, a batch is a sequence of Transact-SQL statements and procedural extensions that are sent to the database system for execution together. The number of statements in a batch is limited by the size of the compiled batch object. The main advantage of a batch over a group of singleton statements is that executing all statements at once brings significant performance benefits.

There are a number of restrictions concerning the appearance of different Transact-SQL statements inside a batch. The most important is that the data definition statements CREATE VIEW, CREATE PROCEDURE, and CREATE TRIGGER must each be the only statement in a batch.

The following sections describe each procedural extension of the Transact-SQL language separately.

Block of Statements

A block allows the building of units with one or more Transact-SQL statements. Every block begins with the BEGIN statement and terminates with the END statement, as shown in the following example:

BEGIN
statement_1
statement_2
…
END

A block can be used inside the IF statement to allow the execution of more than one statement, depending on a certain condition (see Example 8.1).

IF Statement

The Transact-SQL statement IF corresponds to the statement with the same name that is supported by almost all programming languages. IF executes one Transact-SQL statement (or more, enclosed in a block) if a Boolean expression, which follows the keyword IF, evaluates to TRUE. If the IF statement contains an ELSE statement, a second group of statements can be executed if the Boolean expression evaluates to FALSE.

USE sample;
IF (SELECT COUNT(*)
		FROM works_on
		WHERE project_no = 'p1'
		GROUP BY project_no ) > 3
	PRINT 'The number of employees in the project p1 is 4 or more'
	ELSE BEGIN
		PRINT 'The following employees work for the project p1'
		SELECT emp_fname, emp_lname
		FROM employee, works_on
		WHERE employee.emp_no = works_on.emp_no
		AND project_no = 'p1'
	ENDCode language: PHP (php)

Example 8.1 shows the use of a block inside the IF statement. The Boolean expression in the IF statement,

(SELECT COUNT(*)
	FROM works_on
	WHERE project_no = 'p1'
	GROUP BY project_no) > 3Code language: JavaScript (javascript)

is evaluated to TRUE for the sample database. Therefore, the single PRINT statement in the IF part is executed. Notice that this example uses a subquery to return the number of rows (using the COUNT aggregate function) that satisfy the WHERE condition (project_no=’p1′). The result of Example 8.1 is

The number of employees in the project p1 is four or more

WHILE Statement

The WHILE statement repeatedly executes one Transact-SQL statement (or more, enclosed in a block) while the Boolean expression evaluates to TRUE. In other words, if the expression is true, the statement (or block) is executed, and then the expression is evaluated again to determine if the statement (or block) should be executed again. This process repeats until the expression evaluates to FALSE.

A block within the WHILE statement can optionally contain one of two statements used to control the execution of the statements within the block: BREAK or CONTINUE. The BREAK statement stops the execution of the statements inside the block and starts the execution of the statement immediately following this block. The CONTINUE statement stops only the current execution of the statements in the block and starts the execution of the block from its beginning.

Example 8.2 shows the use of the WHILE statement.

USE sample;
WHILE (SELECT SUM(budget)
		FROM project) < 500000
	BEGIN
		UPDATE project SET budget = budget*1.1
		IF (SELECT MAX(budget)
			FROM project) > 240000
		BREAK
		ELSE CONTINUE
	ENDCode language: PHP (php)

In Example 8.2, the budget of all projects will be increased by 10 percent until the sum of budgets is greater than $500,000. However, the repeated execution will be stopped if the budget of one of the projects is greater than $240,000. The execution of Example 8.2 gives the following output:

(3 rows affected)
(3 rows affected)
(3 rows affected)

Local Variables

Local variables are an important procedural extension to the Transact-SQL language. They are used to store values (of any type) within a batch or a routine. They are “local” because they can be referenced only within the same batch in which they were declared. (The Database Engine also supports global variables, which are described in “Scalar Operators, Global Variables“.)

Every local variable in a batch must be defined using the DECLARE statement. (For the syntax of the DECLARE statement, see Example 8.3.) The definition of each variable contains its name and the corresponding data type. Variables are always referenced in a batch using the prefix @. The assignment of a value to a local variable is done

• Using the special form of the SELECT statement
• Using the SET statement
• Directly in the DECLARE statement using the = sign (for instance, @extra_budget MONEY = 1500)

The usage of the first two statements for a value assignment is demonstrated in Example 8.3.

USE sample;
DECLARE @avg_budget MONEY, @extra_budget MONEY
	SET @extra_budget = 15000
	SELECT @avg_budget = AVG(budget) FROM project
	IF (SELECT budget
		FROM project
		WHERE project_no='p1') < @avg_budget
	BEGIN
		UPDATE project
			SET budget = budget + @extra_budget
			WHERE project_no ='p1'
		PRINT 'Budget for p1 increased by @extra_budget'
	END
	ELSE PRINT 'Budget for p1 unchanged'Code language: PHP (php)

The result is

Budget for p1 increased by @extra_budgetCode language: CSS (css)

The batch in Example 8.3 calculates the average of all project budgets and compares this value with the budget of project p1. If the latter value is smaller than the calculated value, the budget of project p1 will be increased by the value of the local variable @extra_budget.

Miscellaneous Procedural Statements

The procedural extensions of the Transact-SQL language also contain the following statements:

• RETURN
• GOTO
• RAISEERROR
• WAITFOR

The RETURN statement has the same functionality inside a batch as the BREAK statement inside WHILE. This means that the RETURN statement causes the execution of the batch to terminate and the first statement following the end of the batch to begin executing.

The GOTO statement branches to a label, which stands in front of a Transact-SQL statement within a batch. The RAISEERROR statement generates a user-defined error message and sets a system error flag. A user-defined error number must be greater than 50000. (All error numbers <= 50000 are system defined and are reserved by the Database Engine.) The error values are stored in the global variable @@error. (Example 17.3 shows the use of the RAISEERROR statement.)

The WAITFOR statement defines either the time interval (if the DELAY option is used) or a specified time (if the TIME option is used) that the system has to wait before executing the next statement in the batch. The syntax of this statement is WAITFOR {DELAY ‘time’ | TIME ‘time’ | TIMEOUT ‘timeout’ }

The DELAY option tells the database system to wait until the specified amount of time has passed. TIME specifies a time in one of the acceptable formats for temporal data. TIMEOUT specifies the amount of time, in milliseconds, to wait for a message to arrive in the queue.