Chapter 10 -- Programming with Transact - SQL

Chapter 10

Microsoft Transact-SQL allows you to use standard SQL as a programming language to write logic that can execute within the database engine. This extremely powerful capability is one of the keys to the success and growth of Microsoft SQL Server. Transact-SQL simplifies application development and reduces the amount of conversation necessary between the client application and the server by allowing more code to be processed at the server.

In this book, I won't delve into all the available syntax of the Transact-SQL language. I'll leave that to the SQL Server documentation. Instead, we'll see the significant programming constructs available, along with some comments and examples. In Chapter 7, I examined the SELECT statement, which is the foundation for effective use of Transact-SQL. Before reading any further, be sure you have a good understanding of the concepts presented in that chapter.

This chapter focuses on the Transact-SQL programming extensions, such as control of flow, looping, error handling, and environment-specific options. These extensions allow you to easily write complex routines entirely in SQL, and they provide the basis for the additional power of Transact-SQL over standard SQL. Later in this chapter, I'll show you some extensive examples that sometimes use Transact-SQL in nonintuitive ways, but it will be far from exhaustive coverage of what is possible using the Transact-SQL language. In the last year, several books dealing specifically with programming in SQL Server have been published, and another one is due out about the same time that this book is. Those books are described in the bibliography, and I strongly suggest you take a look at one or more of them if you want to see the full range of possibilities with SQL Server programming. In Chapter 11, we'll look at batches, stored procedures, and functions. In Chapter 12, we'll look at transactions and triggers. Chapter 13 deals with cursors, which bridge the set-based world of SQL with sequential processing. If you're interested in developing applications using SQL Server, the information in all of these chapters will be important to you.