STOP PRESS: SALMON TRAINING BOOK LAUNCH!

Salmon Training is delighted to announce that our latest Online Book is now available for Microsoft SQL Server.
We're offering a special launch price to customers to celebrate the launch.

Developing Stored Procedures for Microsoft SQL Server

Book Cover

Our e-book takes you step-by-step from basic development to robust high-performance stored procedures.

The 14 chapters are packed with code listings and cover:

  • select, insert, update and delete procedures
  • error handling
  • transactions
  • temporary tables
  • controlling program flow
  • performance tuning
  • programming triggers
  • modular coding

The book is built around a typical business application, and develops a series of stored procedures to support order entry, customer management, product lists, and auditing. You'll be able to transfer the knowledge and code rapidly to your own projects.

We assume no knowledge of stored procedures, but you should have a basic knowledge of SQL and Transact-SQL. If you can write simple SQL queries...you have enough knowledge!

TABLE OF CONTENTS

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 SQL Server Development Environment

You will need a SQL Server database for storing the stored 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

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. SQL 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. SQL 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.

 

Purchase Your Online E-Book

The book is full of stored procedure listings for you to copy-and-paste into a SQL Editor. We provide the book as an Online E-Book, which you access by logging into our online book site using any standard web browser. This means that you must be online to use the book. This is not a PDF download.

Access to the Online E-Book is instant on purchase. When you provide an email address, you are logged in straight away to our online book site and can start reading! We send a link to the book site and your login details to your email address for future use 24X7.

Clickbank sells our products - they are a trusted online retailer specializing in digitally delivered products.

credit cardsCLICK HERE TO PURCHASE

To celebrate the launch, we're offering a special price of $9.50 - act now to get your launch price!

Additional VAT may be added inside the European Union, and where else applicable.

Contact us on "feedback [at] salmontraining.com" if you have any queries.

 

Customers with Internet Explorer 7

Some customers with IE7 have alerted us that the purchase link is not taking them to the payment page. Contact us on "feedback [at] salmontraining.com" and we will send you an alternative link. This is not an issue with all IE7 customers, so please try the main link first.