Bonus Reference Transact-SQL
In Chapter 20, you built SQL statements to retrieve and update rows in a database. You
also learned all the variations of the SELECT statement. Some restrictions, however, can’t be
expressed with a WHERE clause, no matter how complicated. To perform complicated searches,
many programmers would rather write an application to retrieve the desired rows, process them,
and then display some results. The processing could include running totals, formatting of the
query’s output, calculations involving multiple rows of the same table, and so on.
......
Bonus Reference
Transact-SQL
In Chapter 20, you built SQL statements to retrieve and update rows in a database. You
also learned all the variations of the SELECT statement. Some restrictions, however, can’t be
expressed with a WHERE clause, no matter how complicated. To perform complicated searches,
many programmers would rather write an application to retrieve the desired rows, process them,
and then display some results. The processing could include running totals, formatting of the
query’s output, calculations involving multiple rows of the same table, and so on.
Most database management systems include extensions, which enhance SQL and make it
behave more like a programming language. SQL Server provides a set of statements known as
Transact-SQL (T-SQL). T-SQL recognizes statements that fetch rows from one or more tables,
flow-control statements like IF…ELSE and WHILE, and numerous functions to manipulate
strings, numeric values, and dates, similar to Visual Basic’s functions. With T-SQL you can do
everything that can be done with SQL, as well as program these operations. At the very least, you
can attach lengthy SQL statements to a database as stored procedures, so that you can call them by
name. In addition, you can use parameters, so that the same procedure can act on different data.
This chapter is an overview of T-SQL and demonstrates the basic topic with stored proce-
dures that perform complicated tasks on the server. We’ll start with the COMPUTE BY state-
ment, which allows you to calculate totals on groups of the rows retrieved from the database.
This statement looks and feels very much like the straight SQL statements, yet it’s not part of
standard SQL.
Then we’ll look at T-SQL in depth. If you need to look up a function or how to declare a
stored procedure’s arguments, you can use this chapter as reference material. If you’re new to
T-SQL, you should read this material, because T-SQL is a powerful language, and if you’re
working with or plan to switch to SQL Server, you’ll need it sooner or later. In addition, you’ll
improve your Visual Basic programming. T-SQL is the native language of SQL Server. By seeing
how the basic operations can be implemented in T-SQL and VB, you’ll gain a deeper under-
standing of database programming.
chT2 Bonus Reference TRANSACT-SQL
The COMPUTE BY Clause
As you recall from Chapter 20, SQL SELECT statements return a set of rows, all of which
have the same structure. In other words, a SQL query can return either details or totals, but not
both. For example, you can calculate the order totals for all customers with a GROUP BY clause,
but this clause displays totals only. Let’s say you want a list of all customers in the Northwind data-
base, their orders, and the total for each customer. Listing 1 provides a SQL SELECT statement
that retrieves the desired information from the database.
Listing 1: The OrderTotals.sql Query
USE NORTHWIND
SELECT CompanyName, Orders.OrderID,
SUM([Order Details].UnitPrice * Quantity * (1 - Discount))
FROM Products, [Order Details], Customers, Orders
WHERE [Order Details].ProductID = Products.ProductID AND
[Order Details].OrderID = Orders.OrderID AND
Orders.CustomerID=Customers.CustomerID
GROUP BY CompanyName, Orders.OrderID
ORDER BY CompanyName, Orders.OrderID
This statement calculates the totals for each order. The Orders.OrderID field is included in the
GROUP BY clause because it’s part of the SELECT list, but doesn’t appear in an aggregate func-
tion’s arguments. This statement will display groups of customers and the totals for all orders placed
by each customer:
Alfreds Futterkiste 10643 814.5
Alfreds Futterkiste 10692 878.0
Alfreds Futterkiste 10702 330.0
Alfreds Futterkiste 10835 845.80
Alfreds Futterkiste 10952 471.20
Alfreds Futterkiste 11011 933.5
Ana Trujillo Emparedados y helados 10308 88.80
Ana Trujillo Emparedados y helados 10625 479.75
. . .
If you want to see the totals per customer, you must modify Listing 1 as follows:
USE NORTHWIND
SELECT CompanyName,
SUM([Order Details].UnitPrice * Quantity * (1 - Discount))
FROM Products, [Order Details], Customers, Orders
WHERE [Order Details].ProductID = Products.ProductID AND
[Order Details].OrderID = Orders.OrderID AND
Orders.CustomerID=Customers.CustomerID
GROUP BY CompanyName
ORDER BY CompanyName
THE COMPUTE BY CLAUSE chT3
This time I’ve omitted the Orders.OrderID field from the SELECT list and the GROUP BY
clause. This statement will display the total for each customer, since we are not grouping by
OrderID:
Alfreds Futterkiste 4272.9999980926514
Ana Trujillo Emparedados y helados 1402.9500007629395
Antonio Moreno Taquería 7023.9775543212891
Around the Horn 13390.650009155273
Berglunds snabbköp 24927.57746887207
. . .
What we need is a statement that can produce a report of the details with total breaks after each
order and each customer, as shown here (I have shortened the product names to fit the lines on the
printed page without breaks):
Alfreds Futterkiste 10643 Rössle 45.60 15 25 513.00
Alfreds Futterkiste 10643 Chartreuse 18.0 21 25 283.50
Alfreds Futterkiste 10643 Spegesild 12.0 2 25 18.00
814.50
Alfreds Futterkiste 10692 Vegie-spread 43.90 20 0 878.00
878.00
Alfreds Futterkiste 10702 Aniseed Syrup 10.0 6 0 60.00
Alfreds Futterkiste 10702 Lakkalikööri 18.0 15 0 270.00
845.80
Alfreds Futterkiste 10952 Grandma’s 25.00 16 5 380.00
Alfreds Futterkiste 10952 Rössle 45.6 2 0 91.20
471.20
Alfreds Futterkiste 11011 Escargots 13.25 40 5 503.50
Alfreds Futterkiste 11011 Flotemysost 21.50 20 0 430.00
933.50
4273.00
Ana Trujillo 10308 Gudbrand 28.80 1 0 28.80
Ana Trujillo 10308 Outback 12.00 5 0 60.00
88.80
Ana Trujillo 10625 Tofu 23.25 3 0 69.75
Ana Trujillo 10625 Singaporean 14.00 5 0 70.00
Ana Trujillo 10625 Camembert 34.00 10 0 340.00
479.75
T-SQL provides an elegant solution to this problem with the COMPUTE BY clause. The
COMPUTE BY clause calculates aggregate functions (sums, counts, and so on) while a field doesn’t
change value. This field is specified with the BY keyword. When the field changes value, the total
calculated so far is displayed and the aggregate function is reset. To produce the list shown here, you
must calculate the sum of line totals (quantity × price – discount) and group the calculations accord-
ing to OrderID and CustomerID. Listing 2 presents the complete statement that produced the pre-
ceding subtotaled list.
chT4 Bonus Reference TRANSACT-SQL
Listing 2: The OrdersGrouped.sql Query
USE NORTHWIND
SELECT CompanyName, Orders.OrderID, ProductName,
UnitPrice=ROUND([Order Details].UnitPrice, 2),
Quantity,
Discount=CONVERT(int, Discount * 100),
ExtendedPrice=ROUND(CONVERT(money,
Quantity * (1 - Discount) *[Order Details].UnitPrice), 2)
FROM Products, [Order Details], Customers, Orders
WHERE [Order Details].ProductID = Products.ProductID And
[Order Details].OrderID = Orders.OrderID And
Orders.CustomerID = Customers.CustomerID
ORDER BY Customers.CustomerID, Orders.OrderID
COMPUTE SUM(ROUND(CONVERT(money, Quantity * (1 - Discount) *
[Order Details].UnitPrice), 2))
BY Customers.CustomerID, Orders.OrderID
COMPUTE SUM(ROUND(CONVERT(money, Quantity * (1 - Discount) *
[Order Details].UnitPrice), 2))
BY Customers.CustomerID
The first COMPUTE BY clause groups the invoice line totals by order ID within each customer.
The second COMPUTE BY clause groups the same totals by customer, as shown in Figure 1. The
CONVERT() function converts data types similar to the Format() function of VB, and the
ROUND() function rounds a floating-point number. Both functions are discussed later in this chapter.
Figure 1
Using the COM-
PUTE BY clause to
calculate totals on
groups
THE COMPUTE BY CLAUSE chT5
The COMPUTE BY clause can be used with any of the aggregate functions you have seen so far.
Listing 3 displays the order IDs by customer and calculates the total number of invoices issued to
each customer:
Listing 3: The CountInvoices.sql Query
USE NORTHWIND
SELECT Customers.CompanyName, Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerID
COMPUTE COUNT(Orders.OrderID) BY Customers.CustomerID
The SQL engine will count the number of orders while the CustomerID field doesn’t change.
When it runs into a new customer, the current total is displayed and the counter is reset to zero in
anticipation of the next customer. Here’s the output produced by Listing 3:
CompanyName OrderID
---------------------------------------- -----------
Alfreds Futterkiste 10643
Alfreds Futterkiste 10692
Alfreds Futterkiste 10702
Alfreds Futterkiste 10835
Alfreds Futterkiste 10952
Alfreds Futterkiste 11011
=======
6
Ana Trujillo Emparedados y helados 10308
Ana Trujillo Emparedados y helados 10625
Ana Trujillo Emparedados y helados 10759
Ana Trujillo Emparedados y helados 10926
=======
4
In addition to combining multiple COMPUTE BY clauses in the same statement (as we did in
Listing 2), you can add another COMPUTE statement without the BY clause to display a grand total:
USE NORTHWIND
SELECT Customers.CompanyName, Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerID
COMPUTE COUNT(Orders.OrderID) BY Customers.CustomerID
COMPUTE COUNT(Orders.OrderID)
The COMPUTE BY clause requires that the rows are furnished in the proper order, so all the
fields following the BY keyword must also appear in an ORDER BY clause. The COMPUTE BY
chT6 Bonus Reference TRANSACT-SQL
clause will not change the order of the rows to facilitate its calculations. Actually, the SQL engine
will refuse to execute a statement that contains a COMPUTE BY clause but not the equivalent
ORDER clause; it will abort the statement’s execution and display the following error message:
A COMPUTE BY item was not found in the order by list.
All expressions in the compute by list must also be
present in the order by list.
Stored Procedures
A stored procedure is a routine written in T-SQL that acts on the rows of one or more tables.
All SQL statements you have seen so far act on selected rows (they select, update, or delete rows),
but SQL doesn’t provide the means to alter the course of action depending on the values of the
fields. There’s no support for IF statements, no functions to manipulate strings, no formatting func-
tions, and so on. Every DBMS manufacturer extends standard SQL with statements that add the
functionality of a programming language. Access queries, for example, recognize the Mid() function,
which is identical to the VB function by the same name. It extracts part of a string field and uses it
as another field. The equivalent T-SQL function is called SUBSTRING(). In the rest of this chap-
ter, we’ll look at the statements and functions of T-SQL.
Stored procedures are attached to SQL Server databases and become objects of the database, like
tables and views. The simplest application of stored procedures is to attach complicated queries to
the database and call them by name, so that users won’t have to enter them more than once. As you
will see, stored procedures have many more applications, and they can even be used to build business
rules into the database (but more on this later in this chapter).
A stored procedure performs the same calculations as your VB application, only it’s executed on
the server and uses T-SQL statements. T-SQL is practically a programming language. It doesn’t
have a user interface, so you can’t use it to develop full-blown applications, but when it comes to
querying or updating the database and data processing, it can do everything a VB application can do.
You may wonder now, why bother with stored procedures if they don’t do more than VB? The
answer is that T-SQL is SQL Server’s native language, and stored procedures are executed on the
server. A stored procedure can scan thousands of records, perform calculations, and return a single
number to a VB application. If you perform calculations that involve a large number of rows, you
can avoid downloading too much information to the client by writing a stored procedure to do the
work on the server instead. Stored procedures are executed faster than the equivalent VB code
because they’re compiled and they don’t move data from the server to the client.
Another good reason for using stored procedures is that once they’re defined, they become part of
the database and appear to applications as database objects like tables and views. Consider a stored
procedure that adds new orders to a database. This stored procedure is part of the database, and you
can set up the database so that users and applications can’t modify the Orders table directly. By forc-
ing them to go through a stored procedure, you can be sure that all orders are recorded properly. If
you provide a procedure for editing the Orders table, no one can tamper with the integrity of your
data. Moreover, if you change the structure of the underlying tables, you can modify the stored pro-
cedure, and all VB applications that use the stored procedure will continue as before. You can also
implement business rules in the stored procedure (decrease the stock, update a list of best-sellers, and
STORED PROCEDURES chT7
so on). By incorporating all this functionality in to the stored procedure, you simplify the coding of
the client application.
Creating and Executing Stored Procedures
To write, debug, and execute stored procedures against a SQL Server database, you must use the
Query Analyzer. You can also right-click the Stored Procedures item under a database in the Server
Explorer and select New Stored Procedure, as explained in Chapter 20. In this chapter, I will use the
Query Analyzer.
To create a new stored procedure, enter the definition of the procedure in the Query pane and
then press Ctrl+E to execute that definition. This action will attach the procedure to the database,
but it will not actually execute it. To execute a procedure that’s already been stored to the database,
you must use the EXECUTE statement, which is discussed shortly.
To create a new stored procedure and attach it to the current database, use the CREATE PRO-
CEDURE statement. The syntax of the statement is:
CREATE PROCEDURE procedure_name
AS
{ procedure definition }
where procedure_name is the name of the new stored procedure and the statement block following
the AS keyword is the body of the procedure. In its simplest form, a stored procedure is a SQL
statement, like the ones we have discussed so far. If you think you’ll be frequently executing the
AllInvoices query (shown in Listing 4), you can create a stored procedure containing the SQL state-
ment that retrieves customers, orders, and order details. Every time you need this report, you can call
this procedure by name. To create the AllInvoices stored procedure, enter the lines from Listing 4 in
the Query pane of the Query Analyzer.
Listing 4: The AllInvoices Query Stored Procedure
USE NORTHWIND
IF EXISTS (SELECT name FROM sysobjects
WHERE name = ‘AllInvoices’)
DROP PROCEDURE AllInvoices
GO
CREATE PROCEDURE AllInvoices
AS
SELECT CompanyName, Orders.OrderID, ProductName,
UnitPrice=ROUND([Order Details].UnitPrice, 2),
Quantity,
Discount=CONVERT(int, Discount * 100),
ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) *
[Order Details].UnitPrice), 2)
FROM Products, [Order Details], Customers, Orders
WHERE [Order Details].ProductID = Products.ProductID And
[Order Details].OrderID = Orders.OrderID And
Orders.CustomerID=Customers.CustomerID
ORDER BY Customers.CustomerID, Orders.OrderID
chT8 Bonus Reference TRANSACT-SQL
COMPUTE SUM(ROUND(CONVERT(money, Quantity * (1 - Discount) *
[Order Details].UnitPrice), 2))
BY Customers.CustomerID, Orders.OrderID
COMPUTE SUM(ROUND(CONVERT(money, Quantity * (1 - Discount) *
[Order Details].UnitPrice), 2))
BY Customers.CustomerID
Because this is not actually a SQL statement, the first time you execute it, it will not return the list
of invoices. Instead, it will add the AllInvoices procedure to the current database—so be sure to
select the Northwind database in the DB drop-down list, or use the USE keyword to make North-
wind the active database.
If the procedure exists already, you can’t create it again. You must either drop it from the data-
base with the DROP PROCEDURE statement, or modify it with the ALTER PROCEDURE
statement. The syntax of the ALTER PROCEDURE statement is identical to that of the CRE-
ATE PROCEDURE statement. By replacing the CREATE keyword with the ALTER keyword,
you can replace the definition of an existing procedure.
A common approach is to test for the existence of a stored procedure and drop it if it exists. Then,
you can add a new procedure with the CREATE PROCEDURE statement. For example, if you are
not sure the myProcedure procedure exists, use the following statements to find and modify it:
USE DataBase
IF EXISTS (SELECT name FROM sysobjects
WHERE name = ‘myProcedure’)
DROP PROCEDURE myProcedure
GO
CREATE PROCEDURE myProcedure
AS
. . .
The SELECT statement retrieves the name of the desired procedure from the database objects
(again, be sure to execute it against the desired database). If a procedure by the name myProcedure
exists already, EXISTS returns True and drops the procedure definition from the database. Then it
proceeds to add the revised definition.
Once you’ve entered Listing 4 into the Query Analyzer, press Ctrl+E to execute the procedure’s
declaration. If you haven’t misspelled any keywords, the message “The command(s) completed suc-
cessfully.” will appear in the lower pane of the Query Analyzer’s window. When you execute a stored
procedure’s definition, you add it to the database, but the procedure’s statements are not executed.
To execute a stored procedure, you must use the EXECUTE statement (or its abbreviation,
EXEC) followed by the name of the procedure. Assuming that you have created the AllInvoices pro-
cedure, here’s how to execute it.
1. First, clear the Query pane of Query Analyzer, or open a new window in the Query Analyzer.
2. In the fresh Query pane, type:
USE Northwind
EXECUTE AllInvoices
and press Ctrl+E. The result of the query will appear in the Results pane of the Query Analyzer.
STORED PROCEDURES chT9
The first time you execute the procedure, SQL Server will put together an execution plan, so it
will take a few seconds. After that, the procedure’s execution will start immediately, and the rows will
start appearing on the Results pane as soon as they become available.
Tip If a procedure takes too long to execute, or it returns too many rows, you can interrupt it by clicking the Stop but-
ton (a red rectangular button on SQL Server’s toolbar). If you execute an unconditional join by mistake, for example, you
can stop the execution of the query and not have to wait until all rows arrive.
The USE statement isn’t necessary, as long as you remember to select the proper database in the
Analyzer’s window. Since the stored procedure is part of the database, it’s very unlikely that you will
call a stored procedure that doesn’t belong to the current database.
Executing Command Strings
In addition to executing stored procedures, you can use the EXECUTE statement to execute strings
with valid T-SQL statements. If the variable @TSQLcmd contains a valid SQL statement, you can
execute it by passing it as an argument to the EXECUTE procedure:
EXECUTE (@TSQLcmd)
The parentheses are required. If you omit them, SQL Server will attempt to locate the @TSQLcmd
stored procedure. Here’s a simple example of storing SQL statements into variables and executing
them with the EXECUTE method:
DECLARE @Country varchar(20)
DECLARE @TSQLcmd varchar(100)
SET @Country = ‘Germany’
SET @TSQLcmd = ‘SELECT City FROM Customers WHERE Country=”’ + @Country + ‘“‘
EXECUTE (@TSQLcmd)
Tip All T-SQL variables must begin with the @ symbol.
All T-SQL variables must be declared with the DECLARE statement, have a valid data type, and
be set with the SET statement. You will find more information on the use of variables in the follow-
ing sections of this chapter.
The EXECUTE statement with a command string is commonly used to build SQL statements
on the fly. You’ll see a more practical example of this technique in the section “Building SQL State-
ments on the Fly,” later in this chapter.
Tip Statements that are built dynamically and executed with the help of a command string as explained in this section do
not take advantage of the execution plan. Therefore, you should not use this technique frequently. Use it only when you
can’t write the stored procedure at design time.
Why Use Stored Procedures?
Stored procedures are far more than a programming convenience. When a SQL statement, especially
a complicated one, is stored in the database, the database management system (DBMS) can execute
it efficiently. To execute a SQL statement, the query engine must analyze it and put together an
execution plan. The execution plan is analogous to the compilation of a traditional application. The
chT10 Bonus Reference TRANSACT-SQL
DBMS translates the statements in the procedure to statements it can execute directly against the
database. When the SQL statement is stored in the database as a procedure, its execution plan is
designed once and is ready to be used. Moreover, stored procedures can be designed once, tested,
and used by many users and/or applications. If the same stored procedure is used by more than one
user, the DBMS keeps only one copy of the procedure in memory, and all users share the same
instance of the procedure. This means more efficient memory utilization. Finally, you can limit user
access to the database’s tables and force users to access the database through stored procedures. This
is a simple method of enforcing business rules.
Let’s say you have designed a database like Northwind, and you want to update each product’s
stock, and perhaps customer balances, every time a new invoice is issued. You could write the appli-
cations yourself and hope you won’t leave out any of these operations, then explain the structure of
the database to the programmers and hope they’ll follow your instructions. Or you could implement
a stored procedure that accepts the customer’s ID and the IDs and quantities of the items ordered,
then updates all the tables involved in the transaction. Application programmers can call this stored
procedure and never have to worry about remembering to update some of the tables. At a later point,
you may add a table to the database for storing the best-selling products. You can change the stored
procedure, and the client applications that record new orders through this stored procedure need
not be changed. Later in this chapter, you’ll see examples of stored procedures that implement
business rules.
T-SQL: The Language
The basic elements of T-SQL are the same as those of any other programming language: variables,
flow-control statements, and functions. In the following few sections, we’ll go quickly through the
elements of T-SQL. Since this book is addressed to VB programmers, I will not waste any time
explaining what variables are and why they must have a type. I will discuss T-SQL by comparing its
elements to the equivalent VB elements and stress the differences in the statements and functions of
the two languages.
T-SQL Variables
T-SQL is a typed language. Every variable must be declared before it is used. T-SQL supports two
types of variables: local and global. Local variables are declared in the stored procedure’s code, and
their scope is limited to the procedure in which they were declared. The global variables are exposed
by SQL Server, and you can use them without declaring them and from within any procedure.
Local Variables and Data Types
Local variables are declared with the DECLARE statement, and their names must begin with t
he @ character. The following are valid variable names: @CustomerTotal, @Avg_Discount, @i,
@counter. To use them, declare them with the DECLARE statement, whose syntax is:
DECLARE var_name var_type
where var_name is the variable’s name and var_type is the name of a data type supported by SQL
Server. Unlike older versions of Visual Basic, T-SQL doesn’t create variables on the fly. All T-SQL
variables must be declared before they can be used. The available data types are listed here.
T-SQL: THE LANGUAGE chT11
char, varchar
These variables store characters, and their length can’t exceed 8,000 characters. The following state-
ments declare and assign values to char variables:
DECLARE @string1 char(20)
DECLARE @string2 varchar(20)
SET @string1 = ‘STRING1’
SET @string2 = ‘STRING2’
PRINT ‘[‘ + @string1 + ‘]’
PRINT ‘[‘ + @string2 + ‘]’
The last two statements print the following:
[STRING1 ]
[STRING2]
The char variable is padded with spaces to the specified length, while the varchar variable is not.
If the actual data exceeds the specified length of the string, both types truncate the string to its
declared maximum length (to 20 characters, in this example).
nchar, nvarchar
The nchar and nvarchar types are equivalent to the char and varchar types, but they are used for stor-
ing Unicode strings.
bit, bigint, int, smallint, tinyint
Use these types to store whole numbers (integers). Table 1 details their storage and memory
characteristics.
Table 1: T-SQL Integer Data Types
T-SQL Type Equivalent VB Type Memory Range
bit 1 bit 0 or 1
tinyint Byte 1 byte 0 to 255
smallint Short 2 bytes –32,768 to 32,767
int Integer 4 bytes –2,147,483,648 to 2,147,483,647
bigint Long 8 bytes –9,223,372,036,854,775,808 to
9,223,372,036,854,775,807
decimal, numeric
These two types store an integer to the left of the decimal point and a fractional value to the right of
the decimal point. The digits allocated to the integer and fractional part of the number are specified
in the variable’s declaration:
DECLARE @DecimalVar decimal(4, 3)
chT12 Bonus Reference TRANSACT-SQL
The first argument specifies the maximum total number of digits that can be stored to the left
and right of the decimal point. Valid values are in the range 1 through 28. The second argument
specifies the maximum number of digits that can be stored to the right of the decimal point (the
integer part), and its value must be in the range 0 through the specified precision (in other words,
the second argument can’t be larger than the first argument).
If you store the value 30.25 / 4 to the @DecimalVar variable and then print it, it will be dis-
played as 7.563. If the variable was declared as:
DECLARE @DecimalVar decimal(12, 6)
the same value will be printed as 7.562500.
datetime, smalldatetime
Use these two types to store date and time values. The datetime type uses eight bytes: four bytes for
the date and four bytes for the time. The time portion of the datetime type is the number of milli-
seconds after midnight and is updated every 3.33 milliseconds. The smalldatetime type uses four
bytes: two bytes to store the number of days after January 1, 1900, and two bytes to store the num-
ber of minutes since midnight.
The following statements demonstrate how to add days, hours, and minutes to variables of the
datetime and smalldatetime types:
DECLARE @DateVar datetime
DECLARE @smallDateVar smalldatetime
PRINT ‘datetime type’
SET @DateVar = ‘1/1/2000 03:02.10’
PRINT @DateVar
/* Add a day to a datetime variable */
SET @DateVar = @DateVar + 1
PRINT @DateVar
/* Add an hour to a datetime variable */
SET @DateVar = @DateVar + 1.0/24.0
PRINT @DateVar
/* Add a minute to a datetime variable */
SET @DateVar = @DateVar + 1.0/(24.0 * 60.0)
PRINT @DateVar
PRINT ‘smalldatetime type’
SET @smallDateVar = ‘1/1/2000 03:02.10’
PRINT @smallDateVar
/* Add a day to a smalldatetime variable */
SET @smallDateVar = @smallDateVar + 1
PRINT @smallDateVar
/* Add an hour to a smalldatetime variable */
SET @smallDateVar = @smallDateVar + 1.0/24.0
PRINT @smallDateVar
T-SQL: THE LANGUAGE chT13
If you enter these lines in the Query pane of the Query Analyzer and execute them, the following
output will be produced:
datetime type
Jan 1 2000 3:02AM
Jan 2 2000 3:02AM
Jan 2 2000 4:02AM
Jan 2 2000 4:03AM
smalldatetime type
Jan 1 2000 3:02AM
Jan 2 2000 3:02AM
Jan 2 2000 4:02AM
float, real
The float and real types are known as approximate data types and are used for storing floating-point
numbers. These types are known as approximate numeric types, because they store an approximation
of the exact value (which is usually the result of a calculation). The approximation is unavoidable
because binary and decimal fractional values don’t match exactly. The most obvious manifestation of
this approximation is that you can’t represent the value 0 with a float or real variable. If you perform
complicated calculations with floating-point numbers and you want to know whether the result is
zero, do not compare it directly to zero, as it may be a very small number but not exactly zero. Use a
comparison like the following one:
SET @zValue = 1E-20
IF Abs(@result) < @zValue
. . .
ELSE
. . .
The LOG() function returns the logarithm of a numeric value. The following two statements
return the same value:
SET @n1 = log(1 / 0.444009)
SET @n2 = -log(0.444009)
(The variables @n1 and @n2 must be declared as float or real.) If you print the two values with the
PRINT statement, the values will be identical. However, the two values are stored differently inter-
nally, and if you subtract them, their difference will not be zero.
PRINT @n1 - @n2
The result will be the value –4.41415e–008.
money, smallmoney
Use these two types to store currency amounts. Both types use four fractional digits. The money
type uses eight bytes and can represent amounts in the range –922,337,203,685,477.5807 to
922,337,203,685,377.5807. The smallmoney type uses four bytes and can represent amounts in the
range –214,748.3648 to 214,748.3647.
chT14 Bonus Reference TRANSACT-SQL
text
The text data type can store non-Unicode data with a maximum length of 2,147,483,647 characters.
image
This data type can store binary data up to 2,147,483,647 bytes in size.
binary, varbinary
These variables store binary values. The two following statements declare two binary variables, one
with fixed length and another with variable length:
DECLARE @bVar1 binary(4), @bVar2 varbinary
The binary data types can store up to 8,000 bytes. Use binary variables to store the values of
binary columns (small images or other unusual bits of information). To assign a value to a binary
variable, use the hexadecimal notation:
SET @bVar1 = 0x000000F0
This statement assigns the decimal value 255 to the bVar1 variable.
timestamp
This data type holds a counter value that’s unique to the database—an ever-increasing value that
represents the current date and time. Timestamp columns are commonly used to find out whether a
row has been changed since it was read. You can’t extract date and time information from a time-
stamp value, but you can compare two timestamp values to find out whether a row was updated
since it was read, or the order in which rows were read.
Each table can have only one timestamp column, and this column’s values are set by SQL Server
each time a row is inserted or updated. However, you can read this value and compare it to the other
timestamp values.
uniqueidentifier
This is a globally unique identifier (GUID), which is usually assigned to a column with the
NEWID() function. These values are extracted from network card identifiers or other machine-
related information, and they are used in replication schemes to identify rows.
Global Variables
In addition to the local variables you can declare in your procedures, T-SQL supports some global
variables, whose names begin with the symbols @@. These values are maintained by the system,
and you can read them to retrieve system information.
@@FETCH_STATUS
The @@FETCH_STATUS variable is zero if the FETCH statement successfully retrieved a row,
nonzero otherwise. This variable is set after the execution of a FETCH statement and is commonly
used to terminate a WHILE loop that scans a cursor. The global variable @@FETCH_STATUS
T-SQL: THE LANGUAGE chT15
may also have the value –2, which indicates that the row you attempted to retrieve has been deleted
since the cursor was created. This value applies to keyset-driven cursors only.
@@CURSOR_ROWS, @@ROWCOUNT
The @@CURSOR_ROWS global variable returns the number of rows in the most recently
opened cursor, and the @@ROWCOUNT variable returns the number of rows affected by the
action query. The @@ROWCOUNT variable is commonly used with UPDATE and DELETE
statements to find out how many rows were affected by the SQL statement. To find out how many
rows were affected by an update statement, print the @@ROWCOUNT global variable after exe-
cuting the SQL statement:
UPDATE Customers
SET PhoneNumber = ‘031’ + PhoneNumber
WHERE Country = ‘Germany’
PRINT @@ROWCOUNT
@@ERROR
The @@ERROR variable returns an error number for the last T-SQL statement that was exe-
cuted. If this variable is zero, then the statement was executed successfully.
@@IDENTITY
The @@IDENTITY global variable returns the most recently used value for an Identity column.
Identity columns can’t be set; they are assigned a value automatically by the system, each time a new
row is added to the table. Applications usually need this information because Identity fields are used
as foreign keys into other tables. Let’s say you’re adding a new order to the Northwind database. First
you must add a row to the Orders table. You can specify any field’s value, but not the OrderID field’s
value. When the new row is added to the Orders table, you must add rows with the invoice details to
the Order Details table. To do so, you need the value of the OrderID field, which can be retrieved by
the @@IDENTITY global variable. In the section “Implementing Business Rules with Stored Pro-
cedures,” later in this chapter, you’ll find examples on how to use the @@IDENTITY variable.
Other Global Variables
Many global variables relate to administrative tasks, and they are listed in Table 2. T-SQL exposes
more global variables, but the ones listed here are the most common.
Table 2: Commonly Used T-SQL Global Variables
Variable Name Description
@@CONNECTIONS The number of login attempts since SQL Server was last started
@@CPU_BUSY The number of ticks the CPU spent for SQL Server since it was last started
@@IDENTITY The most recently created IDENTITY value
Continued on nextpage
chT16 Bonus Reference TRANSACT-SQL
Table 2: Commonly Used T-SQL Global Variables (continued)
Variable Name Description
@@IDLE The number of ticks SQL Server has been idle since it was last started
@@IO_BUSY The number of ticks SQL Server spent for input/output operations since it was
last started
@@LANGID The current language ID
@@LANGUAGE The current language
@@LOCK_TIMEOUT The current lock-out setting in milliseconds
@@MAX_CONNECTIONS The maximum number of simultaneous connections that can be made to SQL
Server
@@MAX_PRECISION The current precision setting for decimal and numeric data types
@@NESTLEVEL The number of nested transactions for the current execution (transactions can
be nested up to 16 levels)
@@SERVERNAME The name of the local SQL Server
@@TOTAL_ERRORS The number of total errors since SQL was started for the last time
@@TOTAL_READ The number of reads from the disk since SQL Server was last started
@@TOTAL_WRITE The number of writes from the disk since SQL Server was last started
@@TRANCOUNT The number of active transactions for the current user
@@SPID The process ID of the current user process on the server (this number identifies
a process, not a user)
You should consult SQL Server’s online documentation for more information on the global vari-
ables. These variables are used mainly by database administrators, not programmers.
Flow-Control Statements
T-SQL supports the basic flow-control statements that enable you to selectively execute blocks of
statements based on the outcome of a comparison. They are similar to the equivalent VB statements
and, even though there aren’t as many, they are adequate for the purposes of processing rows.
IF…ELSE
This statement executes a block of statements conditionally, and its syntax is:
IF condition
{ statement }
ELSE
{ statement }
T-SQL: THE LANGUAGE chT17
Notice that there’s no THEN keyword and that a T-SQL IF block is not delimited with an
END IF keyword. To execute more than a single statement in the IF or ELSE clauses, you must use
the BEGIN and END keywords to enclose the blocks of statements:
IF condition
BEGIN
{ multiple statements }
END
ELSE
BEGIN
{ multiple statements }
END
Depending on the condition, one of the two blocks of statements are executed. Here’s an example
of the IF…THEN statement with statement blocks:
IF (SELECT COUNT(*) FROM Customers WHERE Country = ‘Germany’) > 0
BEGIN
{ statements to process German customers }
END
ELSE
BEGIN
PRINT “The database contains no customers from Germany”
END
Notice the second pair of BEGIN/END keywords are optional because the ELSE clause consists
of a single statement.
CASE
The CASE statement is equivalent to Visual Basic’s Select Case statement. SELECT is a reserved
SQL keyword and shouldn’t be used with the CASE statement, except as explained shortly. The
CASE statement compares a variable (or field) value against several values and executes a block of
statement, depending on which comparison returns a True result.
A car rental company may need to calculate insurance premiums based on a car’s category. Instead
of multiple IF statements, you can use a CASE structure like the following:
CASE @CarCategory
WHEN ‘COMPACT’ THEN 25.5
WHEN ‘ECONOMY’ THEN 37.5
WHEN ‘LUXURY’ THEN 45.0
END
The CASE statement will return a single value: the one that corresponds to the first WHEN
clause that’s true. Notice this statement is similar to Visual Basic’s Select Case statement, but in T-
SQL, it’s called CASE.
To include the value returned by the CASE statement to the result set, you must combine the
SELECT and CASE statements as shown here:
SELECT @premium=
CASE @CarCategory
WHEN ‘COMPACT’ THEN 25.5
chT18 Bonus Reference TRANSACT-SQL
WHEN ‘ECONOMY’ THEN 37.5
WHEN ‘LUXURY’ THEN 45.0
END
The SELECT keyword in the previous line simply tells SQL Server to display the outcome of the
CASE statement. If the variable @CarCategory is “ECONOMY,” then the value 37.5 is printed in
the Results pane of the Query Analyzer’s window.
T-SQL CASE Statement vs. VB Select Case Statement
As a VB programmer, sooner or later you’ll code a SQL CASE statement as SELECT CASE. The result will
not be an error message. The statement will simply select the result of the CASE statement (SELECT is a T-
SQL keyword that assigns a value to a variable). Let’s clarify this with an example. The following statements
will return the value 7.5. This value will be printed in the Results pane of the Query Analyzer, but you won’t
be able to use it in the statements following the CASE statement.
DECLARE @state char(2)
SET @state = ‘CA’
SELECT CASE @state
WHEN ‘AZ’ THEN 5.5
WHEN ‘CA’ THEN 7.5
WHEN ‘NY’ THEN 8.5
END
If you want to store the result to a variable, use the following syntax instead:
DECLARE @state char(2)
DECLARE @stateTAX real
SET @state = ‘CA’
SET @stateTAX =
CASE @state
WHEN ‘AZ’ THEN 5.5
WHEN ‘CA’ THEN 7.5
WHEN ‘NY’ THEN 8.5
END
PRINT @stateTAX
This syntax has no counterpart in Visual Basic. Note that the entire CASE statement is, in effect, embedded
into the assignment. The @stateTAX variable is set to the value selected by the CASE statement.
WHILE
The WHILE statement repeatedly executes a single statement or a block of T-SQL statements. If
you want to repeat multiple statements, enclose them in a pair of BEGIN/END keywords, as
explained in the description of the IF statement. The most common use of the WHILE statement is
to scan the rows of a cursor, as shown in the following example:
FETCH NEXT INTO variable_list
WHILE @@FETCH_STATUS = 0
T-SQL: THE LANGUAGE chT19
BEGIN
{ statements to process the fields of the current row }
FETCH NEXT INTO variable_list
END
The FETCH NEXT statement reads the next row of a cursor set and stores its fields’ values into
the variables specified in the variable_list, which is a comma-separated list of variables. Finally,
@@FETCH_STATUS is a global variable that returns 0 while there are more records to be
fetched. When we reach the end of the cursor, @@FETCH_STATUS returns –1.
CONTINUE and BREAK
These two keywords are used in conjunction with the WHILE statement to alter the flow of execu-
tion. The CONTINUE keyword ends the current iteration and forces another one. In other words,
the WHILE statement’s condition is evaluated and the loop is re-entered. If the condition is False,
then the WHILE loop is skipped and execution continues with the line following the END key-
word that delimits the loop’s body of statements.
The BREAK keyword terminates the loop immediately and branches to the line following the
loop’s END keyword. The following code segment shows how the two keywords are used in a
WHILE loop:
WHILE
BEGIN
{ read column values into variables }
IF @balance < 0
CONTINUE
IF @balance > 999999
BREAK
{ process @balance variable and/or other variables }
END
This loop reads the rows of a table or cursor and processes only the ones with a positive balance,
less than 1,000,000. If a row with a negative balance is found, the code doesn’t process it and con-
tinues with the next row. If a row with a balance of 1,000,000 or more is found, the code stops pro-
cessing the rows by breaking out of the loop.
GOTO and RETURN
These are the last two flow-control statements, and they enable you to alter the flow of execution by
branching to another location in the procedure. The GOTO statement branches to a line identified
by a label. Here’s a simple example of the GOTO statement (in effect, it’s a less elegant method of
implementing a WHILE loop):
RepeatLoop:
FETCH NEXT INTO variable_list
IF @@FETCH_STATUS = 0
BEGIN
{ process variables }
GOTO RepeatLoop
END
chT20 Bonus Reference TRANSACT-SQL
While more records are in the result set, the GOTO statement branches to the FETCH NEXT
statement. The identifier RepeatLoop is a label (a name identifying the line to which you want to
branch), and it must be followed by a colon. If there are no more records to be fetched and
processed, the procedure continues with the statement following the END keyword.
The RETURN statement ends a procedure unconditionally and, optionally, returns a result. To
return a value from within a stored procedure, use a statement like
RETURN @error_value
@error_value is a local variable, which can be set by the procedure’s code. The calling application,
which could be another stored procedure, should be aware of the possible values returned by the
procedure.
If you don’t specify your own error code, SQL Server returns its error code, which is one of the
values shown in Table 3.
Table 3: RETURN Statement Error Codes
Error Code Description
-1 Missing object
-2 Data type error
-3 Process involved in deadlock
-4 Permission error
-5 Syntax error
-6 User error
-7 Resource error
-8 Internal problem
-9 System limit reached
-10 Internal inconsistency
-11 Internal inconsistency
-12 Corrupt table or index
-13 Corrupt database
-14 Hardware error
Miscellaneous Statements
In addition to the flow-control statements, T-SQL provides other statements as well, of which the
PRINT and RAISERROR statements are frequently used in stored procedures. The PRINT state-
ment shouldn’t normally appear in a stored procedure (it doesn’t produce output that’s returned to
the calling application), but it’s used frequently in quickly debugging a stored procedure.
