Microsoft® SQL Server® 2012 Bible

389

CHAPTER

16

Programming with T-SQL

IN THIS CHAPTER

Exploring 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

Using Multiple Assignment Variable Select Statements

S

tandard SQL Data Manipulation Language (DML) commands — SELECT, INSERT, UPDATE, and

DELETE — modify or return only data. SQL DML lacks both the programming structure to

develop procedures and algorithms, and the database-specifi c 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 ANSI SQL plus its proprietary

collection of extensions to SQL. The purpose of T-SQL is to provide a set of procedural and admin-

istrative tools for the development of a transactional database. You can use these tools 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.

■ It 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 SQL Server as
stored procedures, functions, or triggers.
Truth be told, this book has been covering T-SQL programming since Chapter 6, “Introducing Basic
Query Flow.” The DML commands are the heart of T-SQL. This chapter merely adds the programmatic
elements required to develop server-side procedural code. The language features explained in this
chapter are the foundation for developing stored procedures, user-defi ned functions, and triggers.
c16.indd 389c16.indd 389 7/30/2012 5:38:03 PM7/30/2012 5:38:03 PM
http://www.it-ebooks.info