Reviewing Programming Structures

Every programming language, including T-SQL, has a set way of performing multiple actions, checking whether an action is needed, or performing the same action multiple times. Because a computer processes commands linearly—that is, in a straight line—and program code or SQL script must be presented in a structured format, you will find the different topics in this section very useful. What you will learn in this section are the basic building blocks for this structured style of programming in T-SQL.

Understanding Batches

A batch is a collection of one or more SQL statements that are sent to the server as one group by a client application, such as Query Analyzer. Each batch is then compiled or translated into a single executable unit and plan. If the batch contains multiple SQL statements, all the optimized steps needed to perform the statements are built in the single plan. The ways to specify a batch process are

• All the statements in a stored procedure or trigger comprise a single batch.

• The string executed by an EXECUTE statement.

• All SQL statements send as a single execution unit.

A batch is one or more SQL statements that are sent to the server to be compiled and executed.

When using batches, there are a few rules you must know. They are

• The CREATE statement must begin the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.

• A table cannot be altered and then have the new columns referenced in the same batch.

• If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required.

In the Query Analyzer, a batch is sent by one of the following methods:

• Selecting Query, Execute from the menu

• Clicking the green run arrow button

• Pressing Ctrl+E

If you include the word GO anywhere in the executed text, this will indicate that the client program should send all the SQL statements that precede the GO command, wait for a result, and then continue with the commands that follow it.

Execution Order

Whenever a batch of commands is sent to the server, the SQL code that is sent is processed in order according to the following five steps:

Multiple SQL Statements in a Single Batch

When the server accepts a batch of T-SQL commands, those commands are then put through the process outlined earlier. If any errors are found at any step, with any command in that batch, the process is terminated and an error is returned.

You can place multiple commands in a single batch, which can be compiled and executed as a single group. By taking a look at the following commands, you will be able to get a better idea of how batches work.

use pubs
Select title
From titles
Where title like '%cooking%'
select au_id, au_lname
From authors
Where au_lname = 'Green'

Results:

title
-----------------------------------------------------------------------
Cooking with Computers: Surreptitious Balance Sheets
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
The Psychology of Computer Cooking

(3 row(s) affected)

au_id       au_lname
----------- ----------------------------------------
213-46-8915 Green

(1 row(s) affected)

The two SELECT statements in the example were parsed, compiled, and executed as a group. Each of the SELECT statements returns a result set. However, the server returns the results as a single result set as shown.

To execute these SELECT statements separately, you need to add only a single state- ment to the script. By adding the word GO to the script, you are instructing the Query Analyzer to process this script as two distinct batches.

use pubs
Select title
From titles
Where title like '%cooking%'
GO
select au_id, au_lname
From authors
Where au_lname = 'Green'

I am not showing you the output for this example because it is identical to the previous output. The difference is how the statements are sent to and processed by the server. The Query Analyzer sent the first SELECT statement to the server and then waited for the results to be returned. When the results were completely retrieved, the second command was sent to the server for execution.

Controlling Program Flow

When working in a procedure that contains more than one SQL statement, such as a stored procedure, you need to be able to group multiple SQL statements together according to function. By controlling the flow of execution, you can choose which statements to execute and which ones to skip over, even when to exit a procedure. Table 11.3 lists the T-SQL keywords you can use to control the flow of your SQL scripts.

By combining one or more of these keywords, you can create very complex SQL programs that will perform the required processing for a database.

Using the IF…ELSE

The IF statement enables you to choose to execute one or more statements if a specified condition is TRUE. Additionally, if that same condition is FALSE, you can execute a different set of SQL statements. The syntax of the IF statement is

IF Boolean_expression
    { sql_statement | statement_block}
[ELSE
    { sql_statement | statement_block} ]

This statement can be used to test a variety of conditions within a stored procedure or trigger. The following is a simple example that will test the value of a local variable and then print a different message depending on the value of that variable:

Use northwind
Declare @price money
Select Unitprice
From products
Where productid = 44
Select @price = unitprice
From products
Where productid = 44
If @price < $10
    Print 'Under $10'
Else
   Print 'Equal to or Over $10'

Results:

Unitprice
---------------------
19.4500

(1 row(s) affected)

Equal to or Over $10

The IF test is composed of three basic sections. Immediately after the IF keyword is the Boolean expression that will be tested. If the expression is TRUE, the statement following the test is executed. If the expression is FALSE, the statement following the ELSE is executed. The ELSE clause is optional; if it is not specified and the expression is FALSE, processing will continue by skipping the statement after the IF keyword.

The Boolean expression can consist of one or more expressions that are combined by using the AND and OR keywords. The following IF statement tests two unique expressions and will only execute the following statement if both conditions are TRUE:

If ((@price > $25) and (@type = 'business'))

Using the BEGIN…END

In the syntax of the IF statement, you might have noticed that you can execute one or more statements after the condition is tested. To execute more than one statement in an IF statement, you must group the SQL statements together. This is done by using the BEGIN and END keywords to block a section of code for use with the IF statement or the WHILE loop. Using the BEGIN and END keywords is actually quite simple. To block a group of statements together, simply precede the code with a BEGIN statement and follow it with an END statement. The following example shows an IF statement that executes multiple statements after the test:

USE pubs

IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15
BEGIN
   PRINT 'The following titles are excellent mod_cook books:'
   PRINT ''
   SELECT SUBSTRING(title, 1, 35) AS Title
   FROM titles
   WHERE type = 'mod_cook'
END
ELSE
   IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') > $15
BEGIN
   PRINT 'The following titles are expensive mod_cook books:'
   PRINT ''
   SELECT SUBSTRING(title, 1, 35) AS Title
   FROM titles
   WHERE type = 'mod_cook'
END

If the average price of the select book type is less than $15, one message is printed, whereas another message is printed if the price is greater than $15.

Using the WHILE Statement

Using the WHILE command enables you to repeat a series of SQL statements as long as the condition specified by the WHILE command is TRUE. When you are processing many rows of data, but need to look at the data one row at a time and perform the same actions on each row, the WHILE command provides you with the method for doing so. The following example shows how the WHILE statement works:

Use Northwind
Declare @cntr int
Select @cntr = 1
While @cntr < 6
Begin
       Select Productid, Productname From products Where productid = @cntr
       Select @cntr = @cntr + 1
End

Results:

Productid   Productname

----------- ----------------------------------------
1           Chai

(1 row(s) affected)

Productid   Productname
----------- ----------------------------------------
2           Chang

(1 row(s) affected)
Productid   Productname
----------- ----------------------------------------
3           Aniseed Syrup

(1 row(s) affected)

Productid   Productname
----------- ----------------------------------------
4           Chef Anton's Cajun Seasoning

(1 row(s) affected)

Productid   Productname
----------- ----------------------------------------
5           Chef Anton's Gumbo Mix

(1 row(s) affected)

In this example, I first declared the variable @cntr, which is used to hold the value that is used to select the ProductID in the WHERE clause. The WHILE command specifies to continue while the @cntr variable is less than 6. A BEGIN…END block of SQL is then started where a SELECT statement retrieves the single row of data from the Products table. Finally, the @cntr variable is incremented by 1. When the END statement is executed, the process goes back to the WHILE statement, the condition is tested, and if it is still TRUE, the program block is executed again.

Two extra keywords can be executed within a WHILE loop. If you tried to use them outside of a WHILE loop, an error would occur. These keywords are

Using the PRINT Statement

The PRINT statement can be used to display any type of message to the user. The PRINT statement returns a single line of output instead of a result set as the SELECT statement returns. To show you the difference, the following example will display the same message; first by using the SELECT statement, and then with the PRINT statement:

select 'The following titles are expensive mod_cook books:'
print 'The following titles are expensive mod_cook books:'

Results:

--------------------------------------------------
The following titles are expensive mod_cook books:

(1 row(s) affected)

The following titles are expensive mod_cook books:

You can see that the SELECT statement displays a column title and the row count message, whereas the PRINT statement will display only the requested message. To use the PRINT statement, you would simply pass a string to it. In fact, using many of the available functions, you can create very sophisticated messages with the PRINT statement. The following is an example of this:

print 'The last error number to occur was '+
       convert(varchar(5), @@error)

Results:

The last error number to occur was 0

Using Comments

Comments are nonexecutable text strings that you place in the program code (they are also called remarks). Comments are used to document the functionality of the SQL code or to temporarily disable sections of T-SQL statements and batches that you are testing. Using comments to document SQL code makes any future changes easier. You can use comments to add the following information to a SQL script:

• SQL program name

• Author name

• Date of code changes

• Describe complicated calculations

• Explanation of programming method

SQL Server 2000 supports two different types of commenting syntax:

• -- (double hyphens)—These comment characters can be used on the same line as the code to be executed or by themselves. All text entered after the double hyphen to the end of the current line is part of the comment. If you want multiple-line comments using double hyphens, they must appear at the beginning of each line.

  Select * from Products --This is a comment

• /* */ (forward slash-asterisk pairs)—These characters can be used anywhere in a SQL script. Everything enclosed between the starting and ending pairs is considered a comment. Using this method of creating comments enables you to have a comment that spans multiple lines.

  /* this is also a comment
  on different lines */