Figure 5-2. Query results pane
How It Works
You ask the database to return the data for all columns, and you get exactly that. If you
scroll to the right, you’ll find all the columns in the Employees table.
Most of the time, you should limit queries to only relevant columns. When you select
columns you don’t need, you waste resources. To explicitly select columns, enter the col-
umn names after the
SELECT keyword as shown in the following query and click Execute.
Figure 5-3 shows the results.
Select employeeid, firstname, lastname
from employees
This query selects all the rows from the Employees table but only the EmployeeID,
FirstName, and LastName columns.
CHAPTER 5 ■ MANIPULATING DATABASE DATA 75
9004ch05final.qxd 12/13/07 4:17 PM Page 75
Figure 5-3. Selecting specific columns
Using the WHERE Clause
Queries can have WHERE clauses. The WHERE clause allows you to specify criteria for select-
ing rows. This clause can be complex, but we’ll stick to a simple example for now. The
syntax is as follows:
WHERE <column1> <operator> <column2 / Value>
Here, <operator> is a comparison operator (for example, =, <>, >, or <). (Table 5-1, later in
the chapter, lists the T-SQL comparison operators.)
Try It Out: Refining Your Query
In this exercise, you’ll see how to refine your query.
1. Add the following WHERE clause to the query in Figure 5-3.
Where country = 'USA'
2. R
un the quer
y b
y pressing F5, and you should see the results shown in Figure 5-4.

!= operator and calls <> the not equals oper-
ator
. In fact, standard SQL calls the expressions in a WHERE clause predicates; we’ll use that term because
predicates are either true or false, but other expressions don’t have to be. If you work with another version
of SQL, please refer to its documentation for specifics.
In addition to these operators, the LIKE operator (see Table 5-2) allows you to match
patter
ns in character data. As with all SQL character data, strings must be enclosed in
single quotes (
'). (Chapter 4 co
vers the
LIKE oper
ator in more detail.)
T
able 5-2.
The LIKE O
perator
Operator Description Example
LIKE Allows you to specify a pattern WHERE Title LIKE 'Sales%' selects all rows
where the Title column contains a value that
star
ts with the wor
d
“Sales” followed by zero or
more characters.
CHAPTER 5 ■ MANIPULATING DATABASE DATA78
9004ch05final.qxd 12/13/07 4:17 PM Page 78
You can use four different wildcards in the pattern. Chapter 4 covers these wildcards
in detail, but to briefly review, we list them here in Table 5-3.
Table 5-3. Wildcard Characters

= to IS,
the quer
y will read as
SELECT * FROM employees WHERE Region IS NULL,
and it will return ro
ws where
regions have no value.
To select values in a range or in a set, you can use the BETWEEN and IN operators (see
Table 5-5). The negation of these two is
NOT BETWEEN and NOT IN.
CHAPTER 5 ■ MANIPULATING DATABASE DATA 79
9004ch05final.qxd 12/13/07 4:17 PM Page 79
Table 5-5. The BETWEEN and IN Operators
Operator Description Example
BETWEEN True if a value is within a range. WHERE extension BETWEEN 400 AND 500 returns
the rows where Extension is between 400 and
5
00, inclusive.
IN True if a value is in a list. The list WHERE city IN ('Seattle', 'London') returns
can be the result of a subquery. the rows where City is either Seattle or London.
Combining Predicates
Quite often you’ll need to use more than one predicate to filter your data. You can use the
logical operators shown in Table 5-6.
Table 5-6. SQL Logical Operators
Operator Description Example
AND Combines two expressions, HERE (title LIKE 'Sales%' AND lastname
evaluating the complete ='Peacock')
expression as true only if both
are true
NOT Negates a Boolean value WHERE NOT (title LIKE 'Sales%' AND lastname

FROM <table>
WHERE <predicate>
ORDER BY <column> ASC | DESC
Now that you’ve seen it, you’ll put this syntax to use in an example.
Try It Out:
Writing an Enhanced Query
In this example, you’ll code a query that uses the basic syntax just shown. You want to do
the following:
• Select all the orders that have been handled by employee 5.
• Select the orders shipped to either France or Brazil.
• Display only OrderID, EmployeeID, CustomerID, OrderDate, and ShipCountry.
• Sort the orders by the destination country and the date the order was placed.
Does this sound complicated? Give it a try. Open a New Query window in SQL Server
Management Studio. Enter the following query and click Execute. You should see the
results shown in Figure 5-5.
select orderid,employeeid,customerid,orderdate,shipcountry
from orders
where employeeid = 5 and shipcountry in ('Brazil', 'France')
order by shipcountry asc,orderdate asc
CHAPTER 5 ■ MANIPULATING DATABASE DATA 81
9004ch05final.qxd 12/13/07 4:17 PM Page 81
Figure 5-5. Filtering and sorting data
How It Works
Let’s look at the clauses individually. The SELECT list specifies which columns you want
to use.
select orderid,employeeid,customerid,orderdate,shipcountry
The FROM clause specifies that you want to use the Orders table.
from orders
The WHERE clause is a bit mor
e complicated. I

from the source table. However, the
SELECT INTO clause does have some restrictions: it
will not copy any constraints, indexes, or triggers from the source table.
Try It Out: Creating a New Table
In this exercise, you’ll see how to create a table using a SELECT INTO statement. Open a
New Query window in SQL Server Management Studio Express (remember to make
Northwind your query context). Enter the following query and click Execute. You should
see the results shown in Figure 5-6.
select orderid,employeeid,customerid,orderdate,shipcountry
into #myorder
from orders
CHAPTER 5 ■ MANIPULATING DATABASE DATA 83
9004ch05final.qxd 12/13/07 4:17 PM Page 83
Figure 5-6. Creating a new table
How It Works
In the following statement:
select orderid,employeeid,customerid,orderdate,shipcountry
into #myorder
from orders
you define the SELECT list, the INTO clause with a table name prefixed by #, and then the
FROM clause. This means that you want to retrieve all the specified columns from the
Orders table and insert them into the #myorder table.
E
v
en though y
ou write the query in Northwind, the #myorder table gets created
inside tempdb because of the pr
efixed
# symbol (see F
igur

To try this out, enter the following query, and you should get the results shown in
Figure 5-9.
select orderid,employeeid,customerid,orderdate,shipcountry
into #myemptyorder
from orders
where 0=1
Figure 5-9. Creating an empty table
How It Wor
ks
The magic condition where 0=1, which is a false condition, has done all the work for you,
and only table structure has been copied into the tempdb database.
To view this table, you can navigate to the tempdb database in Object Explorer,
expand the Temporary Tables node if it isn’t already expanded, select the node, right-click
it, and select Refresh to refresh the tables list. You should see the newly created
#myemptyorder table as shown in Figure 5-10.
CHAPTER 5 ■ MANIPULATING DATABASE DATA86
9004ch05final.qxd 12/13/07 4:17 PM Page 86
Figure 5-10. Viewing a newly created empty table in tempdb
As you can see, the table has structure but not data, the false condition you included.
If you were to run a
SELECT query on the #myemptyorder table as shown in
Figure 5-11, the query would return nothing, clearly demonstrating that only the
table structure has been copied because only field names are displayed.
Figure 5-11. W
riting a
SELECT quer
y on an empty table in tempdb
CHAPTER 5 ■ MANIPULATING DATABASE DATA 87
9004ch05final.qxd 12/13/07 4:17 PM Page 87
Inserting Data

insert into shippers ( companyname, phone )
values ('GUIPundits', '+91 9820801756')
Executing this statement in the query pane should produce a Messages window
reporting “(1 row(s) affected)”. You should see the results shown in Figure 5-13.
Figure 5-13. Inserting a new row into the Shippers table
How It Works
The first column, ShipperID, is an identity column, and you can’t insert values into it
explicitly—SQL Server database engine will make sure that a unique and SQL server–
gener
ated v
alue is inserted for the ShipperID field. So, the
INSERT statement needs to be
wr
itten in such a way that y
ou specify the column list you want to insert values for explic-
itly
; though the S
hippers table contains three fields, ShipperID is an identity column, and
it does not expect any v
alue to be inser
ted from the user. But by default, an
INSERT state
-
ment cannot judge whether the column y
ou ar
e not passing a value for is an identity
column.
Thus, to prevent errors, you specify the column list and then pass the respective
v
alues to these fields as shown in the following query:

Now that you’re aware of the implications of the
UPDATE statement, let’s take a good
look at it. In essence, it’s a simple statement that allows you to update values in one or
more rows and columns.
UPDATE <table>
SET <column1> = <value1>, <column2> = <value2>, , <columnN> = <valueN>
WHERE <predicate>
As an example, imagine that the company you added earlier, GUIPundits, has
realized that, though (unfortunately) accurate, its name isn’t good for business, so it’s
changing its name to Pearl HR Solution. To make this change in the database, you first
need to locate the row to change. More than one company could have the same name,
so you shouldn’t use the CompanyName column as the key. Instead, look back at
Figure 5-10 and note the ShipperID value for GUIPundits.
Try It Out: Updating a Row
To change a row’s value, open a New Query window in SQL Server Management Studio
Express. Enter the following query and click Execute.
update shippers
set companyname = 'PearlHRSolution'
where shipperid = 4
How It Works
The ShipperID is the primary key (unique identifier for rows) of the Shippers table, so
you can use it to locate the one row we want to update. Running the query should pro-
duce a Messages pane reporting “(1 row(s) affected)”. Switch back to Object Explorer and
open the Shippers table, and you’ll see that CompanyName has changed, as shown in
Figure 5-15.
CHAPTER 5 ■ MANIPULATING DATABASE DATA 91
9004ch05final.qxd 12/13/07 4:17 PM Page 91
Figure 5-15. The Shippers table after updating a row
When you update more than one column, you still use the SET keyword only once,
and separate column names and their respective values you want to set by comma. For

DELETE FROM <table>
WHERE <predicate>
I
f y
ou need to r
emo
v
e some records from the Shippers table, you need to determine
the pr
imar
y key of the r
o
w y
ou want to remove and use that in the
DELETE statement.
delete from shippers
where shipperid = 4
This should produce a Messages pane reporting “(1 row(s) affected)”. Navigate to the
Table – dbo.Shippers pane, right-click, and select Execute SQL, and you’ll see that the
company has been removed, as shown in Figure 5-17.
CHAPTER 5 ■ MANIPULATING DATABASE DATA 93
9004ch05final.qxd 12/13/07 4:17 PM Page 93
Figure 5-17. The Shippers table after deleting a row
If you try to delete one of the remaining three shippers, you’ll get a database error. A
foreign-key relationship exists from Orders (FK) to Shippers (PK), and SSE enforces it,
preventing deletion of Shippers’ rows that are referred to by Orders rows. If the database
were to allow you to drop records from the PK table, the records in the FK table would be
left as orphan records, leaving the database in an inconsistent state. (Chapter 3 discusses
keys.)
Sometimes you do need to remove every row from a table. In such cases, the

• Deleting stored procedures
Creating Stored Procedures
Stored procedures can have parameters that can be used for input or output and single-
integer
return values (that default to zero), and they can return zero or more result sets.
They can be called from client programs or other stored procedures. Because stored
procedures are so powerful, they are becoming the preferred mode for much database
programming, particularly for multitier applications and web services, since (among
their many benefits) they can dramatically reduce network traffic between clients and
database servers.
95
CHAPTER 6
9004ch06final.qxd 12/13/07 4:16 PM Page 95
Try It Out:Working with a Stored Procedure in SQL Server
Using SQL Server Management Studio Express, you’ll create a stored procedure that pro-
duces a list of the names of employees in the Northwind database. It requires no input
and doesn’t need to set a return value.
1. Open SQL Server Management Studio Express, and in the Connect to Server dia-
log box, select <ServerName>\SQLEXPRESS as the server name and then click
Connect.
2. In Object Explorer, expand the Databases node, select the Northwind database,
and click the New Query button. Enter the following query and click Execute. You
should see the results shown in Figure 6-1.
create procedure sp_Select_All_Employees
as
select
employeeid,
firstname,
lastname
from

cated and can do very powerful things, but that’s well beyond the scope of this book.
■Note The prefix sp_ is a T-SQL convention that typically indicates the stored procedure is coded in SQL.
The prefix
xp_ (which stands for extended procedure) is also used to indicate that the stored procedure isn’t
written in SQL. (However, not all
sp_ stored procedures provided by SQL Server are written in SQL.) By the
way, hundreds of
sp_ (and other) stored procedures are provided by SQL Server 2005 to perform a wide
variety of common tasks.
Although w
e use
sp_ for the purposes of these examples
, it is a best practice not to
create a stored procedure prefixed with
sp_; doing so has a dramatic effect on the search
mechanism and the way the SQL Server database engine starts searching for that partic-
ular procedure in order to execute.
The SQL Server follows this search order if you are executing a stored procedure that
begins with
sp_:
1. SQL Server will search the master database for the existence of the procedure, if it
is available, and then it will call the procedure.
2. If the stored procedure is not available in the master database, SQL Server
searches inside either the database from which you are calling it or the database
whose name you provide as qualifier (
database_name.stored_procedure_name).
Therefore, although a user-created stored procedure prefixed with
sp_ may exist in
the current database, the
master database, which is where the sp_ prefixed stored proce-

create procedure sp_Orders_By_EmployeeId
@employeeid int
as
select orderid, customerid
from orders
where employeeid = @employeeid;
2. To execute the stored procedure, enter the following command along with the
value for the parameter, select it, and then click Execute. You should see the
results shown in Figure 6-3.
execute sp_Orders_By_EmployeeId 2
Figure 6-3. Using an input parameter
CHAPTER 6 ■ USING STORED PROCEDURES 99
9004ch06final.qxd 12/13/07 4:16 PM Page 99