Chapter 20 -- Understanding Advanced T-SQL

3 4

Chapter 20

This chapter covers in detail the Transact-SQL (T-SQL) statements used to modify data that were introduced in earlier chapters; it also describes the T-SQL keywords you can use to control programming flow. You can use these statements and keywords anywhere T-SQL is used—in command lines, scripts, stored procedures, batches, and application programs. In particular, we'll look at the data manipulation statements INSERT, UPDATE, and DELETE (which were touched on in Chapter 13) and the programming constructs IF…ELSE, WHILE, and CASE.

Before we begin our main topic, let's create a table, items, to use for our examples. (We'll create this table in the MyDB database.) The T-SQL commands used to create the items table are shown here:

USE MyDB  GO CREATE TABLE items ( item_category CHAR(20) NOT NULL, item_id SMALLINT NOT NULL, price SMALLMONEY NULL, item_desc VARCHAR(30) DEFAULT 'No desc' ) GO

The item_id column is a good candidate for the IDENTITY property. (See the "Adding the IDENTITY Property" section in Chapter 10 for more information about this property.) But because you cannot explicitly insert values into an identity column, we're not using IDENTITY here. This way, we'll have more flexibility with the examples that use the INSERT statement.



Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net