TABLE OF CONTENTS
GO TO TOP OF PAGE
1. Introduction
This book is designed to be a hands-on experience which will lead you through development of many stored procedures. The book is built around a typical sales application, and develops a series of stored procedures to support order entry, customer management, product listings etc. You will get the most out of the book by working through each example, running the Transact-SQL code in the program listings in every chapter.
2. Setting Up A Development Environment
You will need a Sybase Adaptive Server Enterprise database for storing the procedures, and a SQL editor for development. This chapter discusses several ways of setting up a development environment. Some options are commercially licensed and commonly used in corporate environments. It is also possible to set up a development environment for free.
3. First Stored Procedure - Free Sample Chapter!
In this chapter we dive right into developing a stored procedure. We take the process from requirements analysis, to table design, to creating a stored procedure which utilizes many aspects of typical procedures in complex sales databases. The chapter covers creating and executing stored procedures, input parameters, default parameters, error processing and output parameters.
4. Database Design
In this chapter we complete the design of the data model: the tables that hold the data that our stored procedures will process. This brief chapter works through a typical project lifecycle, from requirements to logical data model to physical data model. This gives an insight into the real world of database development. Its important to run the scripts in this chapter, as they create the tables and test data that will be used in future chapters.
5. Insert Procedures
In chapter 3 we wrote a SELECT stored procedure around the product table. This chapter develops one of the stored procedure involved when a customer places an order to buy a product. The chapter covers error handling, validation processing and performance considerations.
6. Modular Programming
In this chapter, we demonstrate the practice of modularizing stored procedures - providing short procedures that "do one thing", and that can be called from different client processes. This chapter shows how to write modular code and demonstrates how to create "wrapper" stored procedures which call a series of other stored procedures.
7. Error Processing in Stored Procedures
With Transact-SQL, its up to the programmer to trap each error explicitly and do something with it. In this chapter we cover the options for capturing and dealing with errors. Numerous stored procedure listings are shown to demonstrate capturing errors and returning helpful information to the calling applications.
8. Controlling Program Flow within Stored Procedures
In this chapter we look at controlling program flow. Specifically, three different methods of exiting from stored procedures are explored: having multiple exit points, ensuring a single exit point using IF/ELSE constructs, and using a single exit point with the GOTO statement.
9. Update Procedures
This chapter develops an UPDATE procedure which updates a customer's account balance when they place an order. This is a short chapter as the procedure is not complicated, however it leads us into the next very important chapter on transaction management.
10. Transaction Management in Stored Procedures
When our customers use our sample sales application to buy products, an order record is created and their accounts are updated. What if the system crashed between these two actions? The database developer must ensure that the sequence of Transact-SQL statements are treated as "all-or-nothing" – this is what transactions are for. In this chapter, we add transaction processing to our sales application stored procedures.
11. Temporary Tables in Stored Procedures
In this chapter we look at using temporary tables in stored procedures. The chapter looks at the two types of temporary tables, and reasons for using them in stored procedures. The chapter looks in depth at how to pass data between stored procedures using temporary tables.
12. Programming Triggers
In this chapter we build an auditing solution using triggers. Our sample application must audit changes made to customer records. The chapter develops a series of triggers to provide the auditing solution.
13. Performance Guidelines for Stored Procedures
The chapter examines the underlying mechanisms involved when stored procedures are created and executed. Sybase Adaptive Server hides the mechanics of optimization from developers, but we will look "under the hood" to gather some tips for performance. We conclude with a number of general guidelines for developers to follow.
14. Sybase Adaptive Server Security and Stored Procedures
This chapter covers the use of stored procedures to control security in a production environment. Aside from live environments, if you work with other developers on the development database, the chapter shows you know how to grant permission to your colleagues to use your own stored procedures.
GO TO TOP OF PAGE |