SQL Server™ 2005 Bible

Chapter 18. Programming with Transact-SQL

In This Chapter

• The basics of T-SQL and batches

• Working with local variables

• Controlling the flow of the batch

• Exploring SQL Server objects with code

• Working with temporary tables and table variables

• Building dynamic SQL queries

• Using multiple assignment variable select statements

• Trapping and handling errors

Standard SQL Data Manipulation Language (DML) commands—select, insert, update, and delete—only modify or return data. SQL DML lacks both the programming structure to develop procedures and algorithms, and the database-specific commands to control and tune the server. To compensate, each full-featured database product must complement the SQL standard with some proprietary SQL language extension.

Transact-SQL, better known as T-SQL, is Microsoft's implementation of SQL plus its collection of extensions to SQL. The purpose of T-SQL is to provide a set of procedural tools for the development of a transactional database.

T-SQL is often thought of as synonymous with stored procedures. In reality it's much more than that. It may be employed in several different ways within a SQL Server client/server application:

• T-SQL is used within expressions as part of DML commands (insert, update, and delete) submitted by the client process.

• T-SQL is used within blocks of code submitted to SQL Server from a client as a batch or script.

• T-SQL functions are used as expressions within check constraints.

• T-SQL code is used within batches of code that have been packaged within ...