Chapter 1: Stored Procedures Overview

Overview

Welcome to the SQL Server 2000 Stored Procedures handbook. Here, we will cover the different types of stored procedures used in SQL Server 2000 and their usage. We will also examine the working of stored procedures, the potentially tricky areas, and how to avoid them.

Accessing and manipulating data within a database is a fairly straightforward operation. We can build a complete working application with four simple commands – SELECT, INSERT, DELETE, and UPDATE, and perform all the required tasks. The skill in working with databases lies in being able to not just work with these commands, but also complete each unit of work efficiently.

To achieve efficiency, a developer must consider a number of factors during development. However, we will concentrate on running T-SQL from within stored procedures, in this book. Stored procedures can take different forms of design and implementation, but the basics of the code are similar. In this chapter, we will examine what constitutes a stored procedure and discuss different types. We will cover:

• The difference between system stored procedures and user stored procedures

• Creating and designing stored procedures

• Using parameters and best practices

• Flow control statements

• Recursion

• Return values