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 Engine 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.
NOTE To separate DDL statements from one another, use the GO statement. The following sections describe each procedural extension of the Transact-SQL language separately.
1. 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).
2. 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.
NOTE Before you start to execute batches, stored procedures, and UDFs in this chapter, re-create the entire sample database.
Example 8.1
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 = ‘pi’
END
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 = ‘pi’
GROUP BY project_no) > 3
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 pi is 4 or more
NOTE The ELSE part of the IF statement in Example 8.1 contains two statements: PRINT and
SELECT. Therefore, the block with the BEGIN and END statements is required to enclose the two statements. (The PRINT statement is another statement that belongs to procedural extensions; it returns a user-defined message.)
3. 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 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.
Example 8.2
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
END
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
(3 rows affected)
(3 rows affected)
(3 rows affected
NOTE If you want to suppress the output, such as that in Example 8.2 (indicating the number of affected rows in SQL statements), use the SET NOCOUNT ON statement.
4. 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 Chapter 4.)
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 assignment statements for a value assignment is demonstrated in Example 8.3. The batch in this example calculates the average of all project budgets and compares this value with the budget of all projects stored in the project table. If the latter value is smaller than the calculated value, the budget of project pi will be increased by the value of the local variable @extra_budget. We will implement this batch in two different ways. Example 8.3a uses Transact-SQL statements only.
Example 8.3a (using T-SQL only)
— set-oriented way to retrieve values
USE sample;
DECLARE @avg_budget MONEY, @extra_budget MONEY
DECLARE @pr_nr CHAR(4)
SET @extra_budget = 15000
SELECT @avg_budget = AVG(budget) FROM project
IF (SELECT budget
FROM project
WHERE project_no=@pr_nr ) < @avg_budget
BEGIN
UPDATE project
SET budget = budget + @extra_budget
WHERE project_no=@pr_nr
PRINT ‘Budget for @pr_nr increased by @extra_budget’
END
In Example 8.3a only T-SQL statements have been used. The sole use of these statements guarantees that all retrieved rows, independent of their number, will be sent to the system to process them at the same time. (This is called set-oriented processing.)
Example 8.3b applies a concept called cursors.
Example 8.3b (using cursor)
— record-oriented way to retrieve values USE sample;
DECLARE @avg_budget MONEY;
DECLARE @extra_budget MONEY;
DECLARE @budget MONEY;
DECLARE @pr_nr CHAR(4)
DECLARE @P_cursor as CURSOR;
SET @extra_Budget = 15000;
SELECT @avg_budget = AVG(budget) FROM project;
SET @budget = 0;
SET @P_cursor = CURSOR FOR
SELECT project_no, budget FROM project;
OPEN @P_cursor;
FETCH NEXT FROM @P_cursor INTO @pr_nr, @budget
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @pr_nr
PRINT @budget
IF (SELECT budget FROM project
WHERE project_no=@pr_nr) >= @avg_budget
BEGIN
GOTO L1
END
ELSE UPDATE project
SET budget = budget + @extra_budget
WHERE project_no =@pr_nr
PRINT ‘Budget for @pr_nr increased’
L1:
FETCH NEXT FROM @P_cursor INTO @pr_nr, @budget
END
CLOSE @P_cursor;
DEALLOCATE @P_cursor;
In Example 8.3b, the cursor feature is used to solve the same problem as in Example 8.3a. The main difference between these two solutions is that 8.3b retrieves each row from the result separately; i.e., in this case the system processes one record (row) at a time. (For this reason, the type of processing is called record-oriented processing.)
Before discussing the differences between set-oriented processing and record-oriented processing using cursors, I will explain the cursor features in Example 8.3b.
Generally, there are several steps in creating and using cursors in batches and stored procedures. First, you declare your cursor by using the DECLARE statement and assigning the CURSOR data type. After that, you use the SET statement to assign the set of rows, which will be retrieved (one by one) with the cursor. This is followed by opening the cursor using the OPEN statement. Immediately after the OPEN statement is executed, the cursor points before the first row of the selected set of rows.
Now the data processing starts. To move the cursor to the first row in the result set, you use the FETCH NEXT statement:
FETCH NEXT FROM @P_cursor INTO @pr_nr, @budget
This statement fetches a record from the result set and assigns values retrieved with the SELECT statement to the variables @pr_nr and @budget, respectively. The THEN part of the IF statement uses the fetched values to calculate the average of all project budgets and compare the average value with the budget of the particular project.
The WHILE statement uses the system function called @@FETCH_STATUS to create a loop, which will be terminated when all records from the result set are processed. (In other words, this system function returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection. The return value 0 means that the FETCH statement was successful, while -1 specifies that the record (row) was beyond the result set.)
Once all the data has been processed, you use the CLOSE statement to close the cursor. Finally, the DEALLOCATE statement deallocates the particular cursor. The difference between CLOSE and DEALLOCATE is that after the CLOSE statement is executed, you can still reopen the cursor, whereas after the execution of the DEALLOCATE statement, the link between the cursor and the result set is abandoned. (The explicit use of the DEALLOCATE statement is highly recommended, because that way you release all of the internal resources.)
NOTE Do not use the implementation with CURSOR unless absolutely necessary. The record-oriented processing of rows is significantly slower than the set-oriented processing. The more rows that have to be processed, the better performance you will achieve with set-oriented processing.
5. 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 RAISERROR() 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 RAISERROR() 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. (Example 13.8 shows the use of the WAITFOR statement.)
6. Exception Handling with TRY, CATCH, and THROW
The Database Engine supports two statements, TRY and CATCH, to capture and handle exceptions. An exception is a problem (usually an error) that prevents the continuation of a program. In other words, an unhandled exception prevents the application from continuing.
In the case of a handled exception, the existing problem will be relegated to another part of the program, which will handle the exception.
The role of the TRY statement is to capture the exception. (Because this process usually comprises several statements, the term “TRY block” typically is used instead of “TRY statement”) If an exception occurs within the TRY block, the part of the system called the exception handler delivers the exception to the other part of the program, which will handle the exception. This program part is denoted by the keyword CATCH and is therefore called the CATCH block.
NOTE You can handle errors using the @@error global variable (see Example 13.1), but exception handling using the TRY and CATCH statements is the common way modern programming languages like C# and Java treat errors.
Exception handling with the TRY and CATCH blocks gives a programmer a lot of benefits, such as:
- Exceptions provide a clean way to check for errors without cluttering code.
- Exceptions provide a mechanism to signal errors directly rather than using some side effects.
- Exceptions can be seen by the programmer and checked during the compilation process.
The third statement in relation to handling errors is THROW. This statement allows you to throw an exception caught in the exception handling block. Simply stated, the THROW statement is another return mechanism, which behaves similarly to the already described RAISERROR() statement.
Example 8.4 shows how exception handling with the TRY/CATCH/THROW works.
It shows how you can use exception handling to insert all statements in a batch or to roll back the entire statement group if an error occurs. The example is based on the referential integrity between the department and employee tables. For this reason, you have to create both tables using the PRIMARY KEY and FOREIGN KEY clauses, as done in Example 5.11.
Example 8.4
USE sample;
BEGIN TRY
BEGIN TRANSACTION
insert into employee values(11111, ‘Ann’, ‘Smith’,’d2′);
insert into employee values(22222, ‘Matthew’, ‘Jones’,’d4′);
-referential integrity error
insert into employee values(33333, ‘John’, ‘Barrimore’, ‘d2’);
COMMIT TRANSACTION
PRINT ‘Transaction committed’
END TRY
BEGIN CATCH
ROLLBACK
PRINT ‘Transaction rolled back’;
THROW
END CATCH
After the execution of the batch in Example 8.4, all three statements in the batch won’t be executed at all, and the output of this example is
Transaction rolled back
Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the FOREIGN KEY constraint
“foreign_emp”. The conflict occurred in database “sample”, table
“dbo.department”, column ‘dept_no’.
The execution of Example 8.4 works as follows. The first INSERT statement is executed successfully. Then, the second statement causes the referential integrity error. Because all three statements are written inside the TRY block, the exception is “thrown” and the exception handler starts the CATCH block. CATCH rolls back all statements and prints the corresponding message. After that the THROW statement returns the execution of the batch to the caller. For this reason, the content of the employee table won’t change.
NOTE The statements BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK are Transact-SQL statements concerning transactions. These statements start, commit, and roll back transactions, respectively. See Chapter 13 for the discussion of these statements and transactions generally.
Example 8.5 shows the batch that supports server-side paging (for the description of server-side paging, see Chapter 6).
Example 8.5
USE AdventureWorks;
DECLARE
@PageSize TINYINT = 20,
@CurrentPage INT = 4;
SELECT BusinessEntityID, JobTitle, BirthDate
FROM HumanResources.Employee
WHERE Gender = ‘F’
ORDER BY JobTitle
OFFSET (@PageSize * (@CurrentPage – 1)) ROWS
FETCH NEXT @PageSize ROWS ONLY;
The batch in Example 8.5 uses the AdventureWorks database and its Employee table (from the HumanResources schema) to show how generic server-side paging can be implemented. The @Pagesize variable is used with the FETCH NEXT statement to specify the number of rows per page (20, in this case). The other variable, @CurrentPage, specifies which particular page should be displayed. In this example, the content of the third page will be displayed. (The result is not shown because it is too lengthy.)
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.