SQL Server Stored Procedures – Fundamentals

Learning something new is challenging. Learning something new on your own redefines challenging.

Chances are, you are reading my article because you want to learn how to write stored procedures. You are new to this, and you don’t know where to start. You are exactly where I was when I wanted to learn how to write stored procedures. The purpose of this article is to help the developer who doesn’t know where to start. I will give you a place to start, teach you techniques beyond the basic “hello world”, and walk you through examples. I learned how to write stored procedures alone, and have decided to let you use my experiences to your advantage. This article will consist of the following topics:

1. What are stored procedures?

2. What do you need to get started?

3. Writing your first stored procedure

4. Writing more sophisticated stored procedures

   1. Input Variables

   2. Input Variables with SELECT and UPDATE Statements

   3. Exercise:  Pass Data to a SELECT Stored Procedure

   4. Conclusion

   5. Database script for SPFORBEGINNERS (the database referenced in this article)

   At the end, I have also included a SQL script for creating the database used in my examples. It is a small database, and easily replicated. Of course, I am interested in receiving any feedback at tomoneill@deloitte.com.

   What Are Stored Procedures?

   Have you ever written SQL statements, like inserts, selects, and updates? Then you have already written most of a stored procedure. A stored procedure is an already written SQL statement that is saved in the database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure. When you put this SQL statement in a stored procedure, you can then run the stored procedure from the database’s command environment (I am a SQL Server kind of guy, and run stored procedures from the Query Analyzer), using the exec command.

   An example is:

   exec usp_displayallusers

   The name of the stored procedure is “usp_displayallusers”, and “exec” tells SQL Server to execute the code in the stored procedure. (Note: “usp_” in front of the stored procedure name is used to designate this stored procedure as a user-created stored procedure.) The code inside the stored procedure can be something as simple as:

   SELECT * FROM USERLIST

   This “select” statement will return all data in the USERLIST table. You may think, skeptically, that stored procedures aren’t terribly useful. Just save the query and run it when you need to. Too easy, right?

   Well, there is more to the story. Many queries get more complex than “select * from . . .”  Also, you may want to call the stored procedure from an application, such as an ASP page, Visual Basic application, or a Java servlet. With a stored procedure, you can store all the logic in the database, and use a simple command to call the stored procedure. Later, if you decide to migrate from ASP to J2EE, you only need to change the application layer, which will be significantly easier. Much of the business logic will remain in the database.

   Enough background—let’s write some stored procedures.

   Getting Started with Stored Procedures

   What do I need to get started? I have heard that question often. To begin writing stored procedures, the following are essential:

   1. A database management system.

   2. A database built inside the database management system (see the end of this article for a sample).

   3. A text editor, such as Notepad or Query Analyzer.

   Items 1 and 2 are absolutely essential. You can’t write stored procedures without a database. They would be useless. Sometimes, I write my procedures in Notepad (or another text editor), and copy them into the New Stored Procedure window in SQL Server. The New Stored Procedure window is a bit small, and in Notepad I can spread things out a bit (you’ll see later).

   Next, you will have to decide what you want your stored procedure to do. It can be tempting to just dive right into the task at hand, but it is always prudent to sketch out some ideas first. Some considerations should be:

   • Do you want to view data in the database (SELECT), insert new records (INSERT INTO), or do I want to change an existing record (UPDATE)?

   • With which tables will you have to work? Does it make sense to create a VIEW first?

   • How often will this procedure actually be used?

   Once you have struggled with these questions (something of an exaggeration, I guess), you will be ready to start coding!

   Note: Throughout this article, I will focus on stored procedures for SQL Server. You can apply the same principles to other database management systems, but I will make clear references to working in a SQL Server environment.

   Continues…