
Teach Yourself SQL in 21 Days, Second Edition


- Day 19 -
Transact-SQL: An Introduction
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
Day 13, "Advanced SQL Topics," briefly covered static SQL. The examples
on Day 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 book, 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 book 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.
Day 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 Day 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 Day 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 Day 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 Day 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 Day 15, "Streamlining SQL Statements
for Improved Performance."
Summary
Day 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 book 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 yesterday and today 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 Day 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.


© Copyright, Macmillan Computer
Publishing. All rights reserved.