Objectives

Today's material supplements the previous presentations, as Transact-SQL is a supplement to the accepted SQL standard. Today's goals are to

• Identify one of the popular extensions to SQL

• Outline the major features of Transact-SQL

• Provide practical examples to give you an understanding of how Transact-SQL is used

An Overview of Transact-SQL

Chapter 13, "Advanced SQL Topics," briefly covered static SQL. The examples on Chapter 13 depicted the use of embedded SQL in third-generation programming languages such as C. With this method of programming, the embedded SQL code does not change and is, therefore, limited. On the other hand, you can write dynamic SQL to perform the same functions as a procedural programming language and allow conditions to be changed within the SQL code.

As we have mentioned during the discussion of virtually every topic in this guide, almost every database vendor has added many extensions to the language. Transact-SQL is the Sybase and Microsoft SQL Server database product. Oracle's product is PL/SQL. Each of these languages contains the complete functionality of everything we have discussed so far. In addition, each product contains many extensions to the ANSI SQL standard.

Extensions to ANSI SQL

To illustrate the use of these SQL extensions to create actual programming logic, we are using Sybase and Microsoft SQL Server's Transact-SQL language. It contains most of the constructs found in third-generation languages, as well as some SQL Server-specific features that turn out to be very handy tools for the database programmer. (Other manufacturers' extensions contain many of these features and more.)

Who Uses Transact-SQL?

Everyone reading this guide can use Transact-SQL--casual relational database programmers who occasionally write queries as well as developers who write applications and create objects such as triggers and stored procedures.

---

NOTE: Users of Sybase and Microsoft SQL Server who want to explore the true capabilities of relational database programming must use the Transact-SQL features.

---

The Basic Components of Transact-SQL

SQL extensions overcome SQL's limits as a procedural language. For example, Transact-SQL enables you to maintain tight control over your database transactions and to write procedural database programs that practically render the programmer exempt from exhausting programming tasks.

Chapter 19 covers the following key features of Transact-SQL:

• A wide range of data types to optimize data storage

• Program flow commands such as loops and IF-ELSE statements

• Use of variables in SQL statements

• Summarized reports using computations

• Diagnostic features to analyze SQL statements

• Many other options to enhance the standard language of SQL

Data Types

On Chapter 9, "Creating and Maintaining Tables," we discussed data types. When creating tables in SQL, you must specify a specific data type for each column.

---

NOTE: Data types vary between implementations of SQL because of the way each database server stores data. For instance, Oracle uses selected data types, whereas Sybase and Microsoft's SQL Server have their own data types.

---

Sybase and Microsoft's SQL Server support the following data types.

Character Strings

• char stores fixed-length character strings, such as STATE abbreviations, when you know that the column will always be two characters.

• varchar stores variable-length character strings, such as an individual's name, where the exact length of a name is not specified, for example, AL RAY to WILLIAM STEPHENSON.

• text stores strings with nearly unlimited size, such as a remarks column or description of a type of service.

Numeric Data Types

• int stores integers from -2,147,483,647 to +2,147,483,647.

• smallint stores integers from -32,768 to 32,767.

• tinyint stores integers from 0 to 255.

• float expresses numbers as real floating-point numbers with data precisions. Decimals are allowed with these data types. The values range from +2.23E-308 to +1.79E308.

• real expresses real numbers with data precisions from +1.18E-38 to +3.40E38.

DATE Data Types

• datetime values range from Jan 1, 1753 to Dec 31, 9999.

• smalldatetime values range from Jan 1, 1900 to Jun 6, 2079.

Money Data Types

• money stores values up to +922,337,203,685,477.5808.

• smallmoney stores values up to +214,748.3647.

Money values are inserted into a table using the dollar sign; for example:

insert payment_tbl (customer_id, paydate, pay_amt)
values (012845, "May 1, 1997", $2099.99)

Binary Strings

• binary stores fixed-length binary strings.

• varbinary stores variable-length binary strings.

• image stores very large binary strings, for example, photographs and other images.

bit: A Logical Data Type

The data type bit is often used to flag certain rows of data within a table. The value stored within a column whose data type is bit is either a 1 or 0. For example, the value 1 may signify the condition true, whereas 0 denotes a false condition. The following example uses the bit data type to create a table containing individual test scores:

create table test_flag
( ind_id int not null,
  test_results int not null,
  result_flag bit not null)

ANALYSIS:

The column result_flag is defined as a bit column, where the bit character represents either a pass or fail, where pass is true and fail is false.

Throughout the rest of the day, pay attention to the data types used when creating tables and writing Transact-SQL code.

---

NOTE: The code in today's examples uses both uppercase and lowercase. Although SQL keywords are not case sensitive in most implementations of SQL, always check your implementation.

---

Accessing the Database with Transact-SQL

All right, enough talk. To actually run the examples today, you will need to build the following database tables in a database named BASEBALL.

The BASEBALL Database

The BASEBALL database consists of three tables used to track typical baseball information: the BATTERS table, the PITCHERS table, and the TEAMS table. This database will be used in examples throughout the rest of today.

The BATTERS TABLE

NAME char(30)
TEAM int
AVERAGE float
HOMERUNS int
RBIS int

The table above can be created using the following Transact-SQL statement:

INPUT:

1> create database BASEBALL on default
2> go
1> use BASEBALL
2> go
1> create table BATTERS (
2> NAME char(30),
3> TEAM int,
4> AVERAGE float,
5> HOMERUNS int,
6> RBIS int)
7> go

ANALYSIS:

Line 1 creates the database. You specify the database BASEBALL and then create the table BATTERS underneath BASEBALL.

Enter the data in Table 19.1 into the BATTERS table.

---

NOTE: The command go that separates each Transact-SQL statement in the preceding example is not part of Transact-SQL. go's purpose is to pass each statement from a front-end application to SQL Server.

---

Table 19.1. Data for the BATTERS table.

Name	Team	Average	Homeruns	RBIs
Billy Brewster	1	.275	14	46
John Jackson	1	.293	2	29
Phil Hartman	1	.221	13	21
Jim Gehardy	2	.316	29	84
Tom Trawick	2	.258	3	51
Eric Redstone	2	.305	0	28

The PITCHERS Table

The PITCHERS table can be created using the following Transact-SQL statement:

INPUT:

1> use BASEBALL
2> go
1> create table PITCHERS (
2> NAME char(30),
3> TEAM int,
4> WON int,
5> LOST int,
6> ERA float)
7> go

Enter the data in Table 19.2 into the PITCHERS table.

Table 19.2. Data for the PITCHERS table.

Name	Team	Won	Lost	Era
Tom Madden	1	7	5	3.46
Bill Witter	1	8	2	2.75
Jeff Knox	2	2	8	4.82
Hank Arnold	2	13	1	1.93
Tim Smythe	3	4	2	2.76

The TEAMS Table

The TEAMS table can be created using the following Transact-SQL statement:

INPUT:

1> use BASEBALL
2> go
1> create table TEAMS (
2> TEAM_ID int,
3> CITY char(30),
4> NAME char(30),
5> WON int,
6> LOST int,
7> TOTAL_HOME_ATTENDANCE int,
8> AVG_HOME_ATTENDANCE int)
9> go

Enter the data in Table 19.3 into the TEAMS table.

Table 19.3. Data for the TEAMS table.

Team_ID	City	Name	Won	Lost	Total_Home_Attendance	Avg_Home_Attendance
1	Portland	Beavers	72	63	1,226,843	19,473
2	Washington	Representatives	50	85	941,228	14,048
3	Tampa	Sharks	99	36	2,028,652	30,278

Declaring Local Variables

Every programming language enables some method for declaring local (or global) variables that can be used to store data. Transact-SQL is no exception. Declaring a variable using Transact-SQL is an extremely simple procedure. The keyword that must be used is the DECLARE keyword. The syntax looks like this:

SYNTAX:

declare @variable_name data_type

To declare a character string variable to store players' names, use the following statement:

1> declare @name char(30)
2> go

Note the @ symbol before the variable's name. This symbol is required and is used by the query processor to identify variables.

Declaring Global Variables

If you delve further into the Transact-SQL documentation, you will notice that the @@ symbol precedes the names of some system-level variables. This syntax denotes SQL Server global variables that store information.

Declaring your own global variables is particularly useful when using stored procedures. SQL Server also maintains several system global variables that contain information that might be useful to the database system user. Table 19.4 contains the complete list of these variables. The source for this list is the Sybase SQL Server System 10 documentation.

Table 19.4. SQL Server global variables.

Variable Name	Purpose
@@char_convert	0 if character set conversion is in effect.
@@client_csid	Client's character set ID.
@@client_csname	Client's character set name.
@@connections	Number of logons since SQL Server was started.
@@cpu_busy	Amount of time, in ticks, the CPU has been busy since SQL Server was started.
@@error	Contains error status.
@@identity	Last value inserted into an identity column.
@@idle	Amount of time, in ticks, that SQL Server has been idle since started.
@@io_busy	Amount of time, in ticks, that SQL Server has spent doing I/O.
@@isolation	Current isolation level of the Transact-SQL program.
@@langid	Defines local language ID.
@@language	Defines the name of the local language.
@@maxcharlen	Maximum length of a character.
@@max_connections	Maximum number of connections that can be made with SQL Server.
@@ncharsize	Average length of a national character.
@@nestlevel	Nesting level of current execution.
@@pack_received	Number of input packets read by SQL Server since it was started.
@@pack_sent	Number of output packets sent by SQL Server since it was started.
@@packet_errors	Number of errors that have occurred since SQL Server was started.
@@procid	ID of the currently executing stored procedure.
@@rowcount	Number of rows affected by the last command.
@@servername	Name of the local SQL Server.
@@spid	Process ID number of the current process.
@@sqlstatus	Contains status information.
@@textsize	Maximum length of text or image data returned with SELECT statement.
@@thresh_hysteresis	Change in free space required to activate a threshold.
@@timeticks	Number of microseconds per tick.
@@total_errors	Number of errors that have occurred while reading or writing.
@@total_read	Number of disk reads since SQL Server was started.
@@total_write	Number of disk writes since SQL Server was started.
@@tranchained	Current transaction mode of the Transact-SQL program.
@@trancount	Nesting level of transactions.
@@transtate	Current state of a transaction after a statement executes.
@@version	Date of the current version of SQL Server.

Using Variables

The DECLARE keyword enables you to declare several variables with a single statement (although this device can sometimes look confusing when you look at your code later). An example of this type of statement appears here:

1> declare @batter_name char(30), @team int, @average float
2> go

The next section explains how to use variables it to perform useful programming operations.

Using Variables to Store Data

Variables are available only within the current statement block. To execute a block of statements using the Transact-SQL language, the go statement is executed. (Oracle uses the semicolon for the same purpose.) The scope of a variable refers to the usage of the variable within the current Transact-SQL statement.

You cannot initialize variables simply by using the = sign. Try the following statement and note that an error will be returned.

INPUT:

1> declare @name char(30)
2> @name = "Billy Brewster"
3> go

You should have received an error informing you of the improper syntax used in line 2. The proper way to initialize a variable is to use the SELECT command. (Yes, the same command you have already mastered.) Repeat the preceding example using the correct syntax:

INPUT:

1> declare @name char(30)
2> select @name = "Billy Brewster"
3> go

This statement was executed correctly, and if you had inserted additional statements before executing the go statement, the @name variable could have been used.

Retrieving Data into Local Variables

Variables often store data that has been retrieved from the database. They can be used with common SQL commands, such as SELECT, INSERT, UPDATE, and DELETE. Example 19.1 illustrates the use of variables in this manner.

Example 19.1

This example retrieves the name of the player in the BASEBALL database who has the highest batting average and plays for the Portland Beavers.

INPUT:

1> declare @team_id int, @player_name char(30), @max_avg float
2> select @team_id = TEAM_ID from TEAMS where CITY = "Portland"
3> select @max_avg = max(AVERAGE) from BATTERS where TEAM = @team_id
4> select @player_name = NAME from BATTERS where AVERAGE = @max_avg
5> go

ANALYSIS:

This example was broken down into three queries to illustrate the use of variables.

The PRINT Command

One other useful feature of Transact-SQL is the PRINT command that enables you to print output to the display device. This command has the following syntax:

SYNTAX:

PRINT character_string

Although PRINT displays only character strings, Transact-SQL provides a number of useful functions that can convert different data types to strings (and vice versa).

Example 19.2

Example 19.2 repeats Example 19.1 but prints the player's name at the end.

INPUT:

1> declare @team_id int, @player_name char(30), @max_avg float
2> select @team_id = TEAM_ID from TEAMS where CITY = "Portland"
3> select @max_avg = max(AVERAGE) from BATTERS where TEAM = @team_id
4> select @player_name = NAME from BATTERS where AVERAGE = @max_avg
5> print @player_name
6> go

Note that a variable can be used within a WHERE clause (or any other clause) just as if it were a constant value.

Flow Control

Probably the most powerful set of Transact-SQL features involves its capability to control program flow. If you have programmed with other popular languages such as C, COBOL, Pascal, and Visual Basic, then you are probably already familiar with control commands such as IF...THEN statements and loops. This section contains some of the major commands that allow you to enforce program flow control.

BEGIN and END Statements

Transact-SQL uses the BEGIN and END statements to signify the beginning and ending points of blocks of code. Other languages use brackets ({}) or some other operator to signify the beginning and ending points of functional groups of code. These statements are often combined with IF...ELSE statements and WHILE loops. Here is a sample block using BEGIN and END:

SYNTAX:

BEGIN
  statement1
  statement2
  statement3...
END

IF...ELSE Statements

One of the most basic programming constructs is the IF...ELSE statement. Nearly every programming language supports this construct, and it is extremely useful for checking the value of data retrieved from the database. The Transact-SQL syntax for the IF...ELSE statement looks like this:

SYNTAX:

if (condition)
begin
     (statement block)
end
else if (condition)
begin
     statement block)
end
.
.
.
else
begin
     (statement block)
end

Note that for each condition that might be true, a new BEGIN/END block of statements was entered. Also, it is considered good programming practice to indent statement blocks a set amount of spaces and to keep this number of spaces the same throughout your application. This visual convention greatly improves the readability of the program and cuts down on silly errors that are often caused by simply misreading the code.

Example 19.3

Example 19.3 extends Example 19.2 by checking the player's batting average. If the player's average is over .300, the owner wants to give him a raise. Otherwise, the owner could really care less about the player!

Example 19.3 uses the IF...ELSE statement to evaluate conditions within the statement. If the first condition is true, then specified text is printed; alternative text is printed under any other conditions (ELSE).

INPUT:

1> declare @team_id int, @player_name char(30), @max_avg float
2> select @team_id = TEAM_ID from TEAMS where CITY = "Portland"
3> select @max_avg = max(AVERAGE) from BATTERS where TEAM = @team_id
4> select @player_name = NAME from BATTERS where AVERAGE = @max_avg
5> if (@max_avg > .300)
6> begin
7>      print @player_name
8>      print "Give this guy a raise!"
9> end
10> else
11> begin
12>      print @player_name
13>      print "Come back when you're hitting better!"
14> end
15> go

Example 19.4

This new IF statement enables you to add some programming logic to the simple BASEBALL database queries. Example 19.4 adds an IF...ELSE IF...ELSE branch to the code in Ex- ample 19.3.

INPUT:

1> declare @team_id int, @player_name char(30), @max_avg float
2> select @team_id = TEAM_ID from TEAMS where CITY = "Portland"
3> select @max_avg = max(AVERAGE) from BATTERS where TEAM = @team_id
4> select @player_name = NAME from BATTERS where AVERAGE = @max_avg
5> if (@max_avg > .300)
6> begin
7>      print @player_name
8>      print "Give this guy a raise!"
9> end
10> else if (@max_avg > .275)
11> begin
12>     print @player_name
13>     print "Not bad.  Here's a bonus!"
14> end
15> else
16> begin
17>      print @player_name
18>      print "Come back when you're hitting better!"
19> end
20> go

Transact-SQL also enables you to check for a condition associated with an IF statement. These functions can test for certain conditions or values. If the function returns TRUE, the IF branch is executed. Otherwise, if provided, the ELSE branch is executed, as you saw in the previous example.

The EXISTS Condition

The EXISTS keyword ensures that a value is returned from a SELECT statement. If a value is returned, the IF statement is executed. Example 19.5 illustrates this logic.

Example 19.5

In this example the EXISTS keyword evaluates a condition in the IF. The condition is specified by using a SELECT statement.

INPUT:

1> if exists (select * from TEAMS where TEAM_ID > 5)
2> begin
3>      print "IT EXISTS!!"
4> end
5> else
6> begin
7>      print "NO ESTA AQUI!"
8> end

Testing a Query's Result

The IF statement can also test the result returned from a SELECT query. Example 19.6 implements this feature to check for the maximum batting average among players.

Example 19.6

This example is similar to Example 19.5 in that it uses the SELECT statement to define a condition. This time, however, we are testing the condition with the greater than sign (>).

INPUT:

1> if (select max(AVG) from BATTERS) > .400
2> begin
3>      print "UNBELIEVABLE!!"
4> end
5> else
6>      print "TED WILLIAMS IS GETTING LONELY!"
7> end

We recommend experimenting with your SQL implementation's IF statement. Think of several conditions you would be interested in checking in the BASEBALL (or any other) database. Run some queries making use of the IF statement to familiarize yourself with its use.

The WHILE Loop

Another popular programming construct that Transact-SQL supports is the WHILE loop. This command has the following syntax:

SYNTAX:

WHILE logical_expression
     statement(s)

Example 19.7

The WHILE loop continues to loop through its statements until the logical expression it is checking returns a FALSE. This example uses a simple WHILE loop to increment a local variable (named COUNT).

INPUT:

1> declare @COUNT int
2> select @COUNT = 1
3> while (@COUNT < 10)
4> begin
5>      select @COUNT = @COUNT + 1
6>      print "LOOP AGAIN!"
7> end
8> print "LOOP FINISHED!"

---

NOTE: Example 19.7 implements a simple FOR loop. Other implementations of SQL, such as Oracle's PL/SQL, actually provide a FOR loop statement. Check your documentation to determine whether the system you are using supports this useful command.

---

The BREAK Command

You can issue the BREAK command within a WHILE loop to force an immediate exit from the loop. The BREAK command is often used along with an IF test to check some condition. If the condition check succeeds, you can use the BREAK command to exit from the WHILE loop. Commands immediately following the END command are then executed. Example 19.8 illustrates a simple use of the BREAK command. It checks for some arbitrary number (say @COUNT = 8). When this condition is met, it breaks out of the WHILE loop.

Example 19.8

Notice the placement of the BREAK statement after the evaluation of the first condition in the IF.

INPUT:

1> declare @COUNT int
2> select @COUNT = 1
3> while (@COUNT < 10)
4> begin
5>      select @COUNT = @COUNT + 1
6>      if (@COUNT = 8)
7>      begin
8>            break
9>      end
10>     else
11>     begin
12>           print "LOOP AGAIN!"
13>     end
14> end
15> print "LOOP FINISHED!"

ANALYSIS:

The BREAK command caused the loop to be exited when the @COUNT variable equaled 8.

The CONTINUE Command

The CONTINUE command is also a special command that can be executed from within a WHILE loop. The CONTINUE command forces the loop to immediately jump back to the beginning, rather than executing the remainder of the loop and then jumping back to the beginning. Like the BREAK command, the CONTINUE command is often used with an IF statement to check for some condition and then force an action, as shown in Example 19.9.

Example 19.9

Notice the placement of the CONTINUE statement after the evaluation of the first condition in the IF.

INPUT:

1> declare @COUNT int
2> select @COUNT = 1
3> while (@COUNT < 10)
4> begin
5>      select @COUNT = @COUNT + 1
6>      if (@COUNT = 8)
7>      begin
8>            continue
9>      end
10>     else
11>     begin
12>           print "LOOP AGAIN!"
13>     end
14> end
15> print "LOOP FINISHED!"

ANALYSIS:

Example 19.9 is identical to Example 19.8 except that the CONTINUE command replaces the BREAK command. Now instead of exiting the loop when @COUNT = 8, it simply jumps back to the top of the WHILE statement and continues.

Using the WHILE Loop to Scroll Through a Table

SQL Server and many other database systems have a special type of object--the cursor--that enables you to scroll through a table's records one record at a time. (Refer to Chapter 13.) However, some database systems (including SQL Server pre-System 10) do not support the use of scrollable cursors. Example 19.10 gives you an idea of how to use a WHILE loop to implement a rough cursor-type functionality when that functionality is not automatically supplied.

Example 19.10

You can use the WHILE loop to scroll through tables one record at a time. Transact-SQL stores the rowcount variable that can be set to tell SQL Server to return only one row at a time during a query. If you are using another database product, determine whether your product has a similar setting. By setting rowcount to 1 (its default is 0, which means unlimited), SQL Server returns only one record at a time from a SELECT query. You can use this one record to perform whatever operations you need to perform. By selecting the contents of a table into a temporary table that is deleted at the end of the operation, you can select out one row at a time, deleting that row when you are finished. When all the rows have been selected out of the table, you have gone through every row in the table! (As we said, this is a very rough cursor functionality!) Let's run the example now.

INPUT:

1> set rowcount 1
2> declare @PLAYER char(30)
3> create table temp_BATTERS (
4> NAME char(30),
5> TEAM int,
6> AVERAGE float,
7> HOMERUNS int,
8> RBIS int)
9> insert temp_BATTERS
10> select * from BATTERS
11> while exists (select * from temp_BATTERS)
12> begin
13>      select @PLAYER = NAME from temp_BATTERS
14>      print @PLAYER
15>      delete from temp_BATTERS where NAME = @PLAYER
16> end
17> print "LOOP IS DONE!"

ANALYSIS:

Note that by setting the rowcount variable, you are simply modifying the number of rows returned from a SELECT. If the WHERE clause of the DELETE command returned five rows, five rows would be deleted! Also note that the rowcount variable can be reset repeatedly. Therefore, from within the loop, you can query the database for some additional information by simply resetting rowcount to 1 before continuing with the loop.

Transact-SQL Wildcard Operators

The concept of using wildcard conditions in SQL was introduced on Chapter 3, "Expressions, Conditions, and Operators." The LIKE operator enables you to use wildcard conditions in your SQL statements. Transact-SQL extends the flexibility of wildcard conditions. A summary of Transact-SQL's wildcard operators follows.

• The underscore character (_)represents any one individual character. For example, _MITH tells the query to look for a five-character string ending with MITH.

• The percent sign (%) represents any one or multiple characters. For example, WILL% returns the value WILLIAMS if it exists. WILL% returns the value WILL.

• Brackets ([ ]) allow a query to search for characters that are contained within the brackets. For example, [ABC] tells the query to search for strings containing the letters A, B, or C.

• The ^ character used within the brackets tells a query to look for any characters that are not listed within the brackets. For example, [^ABC] tells the query to search for strings that do not contain the letters A, B, or C.

Creating Summarized Reports Using COMPUTE

Transact-SQL also has a mechanism for creating summarized database reports. The command, COMPUTE, has very similar syntax to its counterpart in SQL*Plus. (See Chapter 20, "SQL*Plus.")

The following query produces a report showing all batters, the number of home runs hit by each batter, and the total number of home runs hit by all batters:

INPUT:

select name, homeruns
from batters
compute sum(homeruns)

ANALYSIS:

In the previous example, COMPUTE alone performs computations on the report as a whole, whereas COMPUTE BY performs computations on specified groups and the entire report, as the following example shows:

SYNTAX:

COMPUTE FUNCTION(expression) [BY expression]
  where the FUNCTION might include SUM, MAX, MIN, etc. and
  EXPRESSION is usually a column name or alias.

Date Conversions

Sybase and Microsoft's SQL Server can insert dates into a table in various formats; they can also extract dates in several different types of formats. This section shows you how to use SQL Server's CONVERT command to manipulate the way a date is displayed.

SYNTAX:

CONVERT (datatype [(length)], expression, format)

The following date formats are available with SQL Server when using the CONVERT function:

Format code	Format picture
100	mon dd yyyy hh:miAM/PM
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	mon dd, yy
108	hh:mi:ss
109	mon dd, yyyy hh:mi:ss:mmmAM/PM
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd

INPUT:

select "PayDate" = convert(char(15), paydate, 107)
from payment_table
where customer_id = 012845

OUTPUT:

PayDate
---------------
May 1, 1997

ANALYSIS:

The preceding example uses the format code 107 with the CONVERT function. According to the date format table, code 107 will display the date in the format mon dd, yy.

SQL Server Diagnostic Tools--SET Commands

Transact-SQL provides a list of SET commands that enable you to turn on various options that help you analyze Transact-SQL statements. Here are some of the popular SET commands:

• SET STATISTICS IO ON tells the server to return the number of logical and physical page requests.

• SET STATISTICS TIME ON tells the server to display the execution time of an SQL statement.

• SET SHOWPLAN ON tells the server to show the execution plan for the designated query.

• SET NOEXEC ON tells the server to parse the designated query, but not to execute it.

• SET PARSONLY ON tells the server to check for syntax for the designated query, but not to execute it.

Transact-SQL also has the following commands that help to control what is displayed as part of the output from your queries:

• SET ROWCOUNT n tells the server to display only the first n records retrieved from a query.

• SET NOCOUNT ON tells the server not to report the number of rows returned by a query.

---

NOTE: If you are concerned with tuning your SQL statements, refer to Chapter 15, "Streamlining SQL Statements for Improved Performance."

---

Summary

Chapter 19 introduces a number of topics that add some teeth to your SQL programming expertise. The basic SQL topics that you learned earlier in this guide are extremely important and provide the foundation for all database programming work you undertake. However, these topics are just a foundation. The SQL procedural language concepts explained yesterChapter and toChapter build on your foundation of SQL. They give you, the database programmer, a great deal of power when accessing data in your relational database.

The Transact-SQL language included with the Microsoft and Sybase SQL Server database products provide many of the programming constructs found in popular third- and fourth-generation languages. Its features include the IF statement, the WHILE loop, and the capability to declare and use local and global variables.

Keep in mind that Chapter 19 is a brief introduction to the features and techniques of Transact-SQL code. Feel free to dive head first into your documentation and experiment with all the tools that are available to you. For more detailed coverage of Transact-SQL, refer to the Microsoft SQL Server Transact-SQL documentation.

Q&A

Q Does SQL provide a FOR loop?

A Programming constructs such as the FOR loop, the WHILE loop, and the CASE statement are extensions to ANSI SQL. Therefore, the use of these items varies widely among database systems. For instance, Oracle provides the FOR loop, whereas Transact-SQL (SQL Server) does not. Of course, a WHILE loop can increment a variable within the loop, which can simulate the FOR loop.

Q I am developing a Windows (or Macintosh) application in which the user interface consists of Windows GUI elements, such as windows and dialog boxes. Can I use the PRINT statement to issue messages to the user?

A SQL is entirely platform independent. Therefore, issuing the PRINT statement will not pop up a message box. To output messages to the user, your SQL procedures can return predetermined values that indicate success or failure. Then the user can be notified of the status of the queries. (The PRINT command is most useful for debugging because a PRINT statement executed within a stored procedure will not be output to the screen anyway.)

Workshop

The Workshop provides quiz questions to help solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you have learned. Try to answer the quiz and exercise questions before checking the answers in Appendix F, "Answers to Quizzes and Exercises."

Quiz

1. True or False: The use of the word SQL in Oracle's PL/SQL and Microsoft/Sybase's Transact-SQL implies that these products are fully compliant with the ANSI standard.

2. True or False: Static SQL is less flexible than Dynamic SQL, although the performance of static SQL can be better.

Exercises

1. If you are not using Sybase/Microsoft SQL Server, compare your product's extensions to ANSI SQL to the extensions mentioned today.

2. Write a brief set of statements that will check for the existence of some condition. If this condition is true, perform some operation. Otherwise, perform another operation.

---