HostMySite.Com is sponsoring this tutorial, please visit their site today!

Introduction to Stored Procedures by: Robert Bailey

[Download in PDF Format] [Download in FlashPaper Format]

So you want to learn to write a stored procedure for Microsoft SQL Server? This tutorial will touch on the basics, there are many things you can do with a stored procedure that can not be addressed in this small tutorial, but we will get you started. 

Since I am guessing you have MS SQL if you are reading this, we will create the tables we are going to need for this exercise. Open up the SQL Query Analyzer. This can be found in the Microsoft SQL Server program group, appropriately titled “Query Analyzer”. If you do not have the client tools installed and do not have access, take a look on the net, you will find many out there to use. Since this is not for beginners, I will not be describing the basic SQL statements I will be using to create these tables. You can copy and paste these commands if you would like as well. Also, make sure you login as SA as we are not going to be worrying about permissions in this tutorial as well. This is just to get you started with Stored Procedures. 

First, let us create the database:

create database "SP_Tutorial"

Now let’s create a simple table in the database for us to use:

    use SP_Tutorial
    Create Table Employees( 
        EmployeeID Int Not Null,
        First_Name Char (50) Not Null,
        Last_Name Char (50) Not Null,
        Address VarChar (64) Not Null,
        City Char (50) Not Null,
        State Char (2) Not Null,
        Constraint EmployeePK Primary Key ( EmployeeID ) 
        )


Now we have the database and the table. Let’s insert some values real fast then get started on our Stored Procedures:

use SP_Tutorial
    Insert into Employees (
            First_Name, 
            Last_Name, 
            Address, 
            City, 
            State
        )
        Values (
            'Robert', 
            'Bailey', 
            '111 Main Street',
            'Seattle',
            'WA'
        )

Insert into Employees (
            First_Name, 
            Last_Name, 
            Address, 
            City, 
            State
        )
        Values (
            'Daffy',
              'Duck',
            '123 Mallard Court',
            'Baltimore',
            'MD'
        )

Insert into Employees (
               First_Name,
               Last_Name,
               Address,
                  City,
               State
            )
            Values (
                'Mickey',
                'Mouse',
                '1 Rat Ave',
                'Tampa',
                'FL'
            )

Now we have 3 “Employees” in the database. Let’s first write a Stored Procedure to retrieve all the employees, then we will work from there.

CREATE PROCEDURE dbo.sp_get_all_employees AS
select *
from employees

This is a very simple stored procedure. This will simply return all the records in this database. You will see that we added the command “CREATE PROCEDURE”, so the server knows we are creating a procedure, and we followed it by the name, appended by the owner. We set the owner as DBO, and the procedure we named sp_get_all_employees. Then we wrote “AS” so that it knew we were going to be following with our SQL statement. I generally start my procedures with sp_ so that I know it is a procedure. Now let’s run our first Stored Procedure. We run it by giving the execute command:

execute dbo.sp_get_all_employees

We can also truncate our execute command such as this:

exec dbo.sp_get_all_employees

They both will do the same thing. Did you run it? Returned everything in the table, right? Congratulations, you have written your first Stored Procedure! Now let’s try using some parameters in the procedure.

Say we want to pass it a last name, and then return all the data for the person that matches the last name we have passed it. 

We here is the code needed to write this procedure:

CREATE PROCEDURE dbo.sp_get_all_employee_details
@P_LastName Char (50)
AS
select *
from employees
Where Last_Name = @P_LastName

Now notice that we changed the name to sp_get_all_employee_details. It is pretty descriptive, so we do not loose track of the procedures that we do write. Now after we have named the procedure, you will also see that it does not say “AS”. This is because we first need to declare our parameters. We are only using one with this procedure, and we named it @P_LastName, since that is what we want, remember to be descriptive if you can. After we declare the parameter name, we tell the procedure what to expect, and with this we are expecting a Character string (defined by Char) that will not exceed 50 characters. When we created the database we limited this column to 50 characters, so there is no need to go over this limit. If you want to add more variables, just add a comma at the end of each declared parameter, as in true SQL fashion, but I am sure you guessed that. You do not have to use @P_, but again, this is a naming convention that I use for all my parameters in Stored Procedures. 

After we have declared all our parameters we then tell the procedure that we will be starting our SQL statement now, so we go ahead and add the “AS” then we add our SQL statement. We can use the passed parameter now by addressing it as we have declared it, in this example it is @P_LastName.

Go ahead and run it and make sure it works:

exec sp_get_all_employee_details
@P_LastName = 'Mouse'

Now we will alter the procedure and create a procedure that takes two parameters, a first name and a last name:

ALTER PROCEDURE dbo.sp_get_all_employee_details
@P_LastName Char (50),
@P_FirstName CHAR (50)
AS
select *
from employees
Where Last_Name = @P_LastName
and First_Name = @P_FirstName

Now notice that I did not start with CREATE, since it has already been created, I simply want to alter the procedure, so I start with ALTER then the procedure name that I want to alter. After this, I declare my 2 parameters that I want to use, @P_LastName and @P_FirstName. I add one more line of SQL, so that I will be looking for both the first and last name. Now lets run this one, and see how it goes:

exec sp_get_all_employee_details
@P_LastName = Bailey ,
@P_FirstName = Robert

Notice that there is also a comma between the parameters when I call the procedure. This is important, or it will just crash on you. You must pass the parameters as they are declared in the procedure, so if @P_FirstName was declared first, you must pass this first when executing the procedure.

Another SQL tip for you: Try to steer away from using wildcards in your select statements. I used them here only to keep the code smaller and easier to read, but when you use wildcards in your select statements, it does not use the indexes, thus slowing up your statements execution. Now with this small table it really did not matter, but if you are interested in using stored procedures, I would imagine that one of your concerns are speed of execution, which stored procedures do wonders for. Thanks for taking the time to read this tutorial. This was just a small introduction on stored procedures and not meant to be a reference at all, just a place to start. 

I am willing to add more tutorials on stored procedures if there is an interest, so please write me at RBailey@Tinetics.com if you would like to see more stored procedure tutorials.

HostMySite.Com is sponsoring this tutorial, please visit their site today!

Please rate this tutorial:

Post a new comment on this tutorial
post a new comment on this particular tutorial
Your Name:
Your Email:
Comment Title:
Comments:
Key Phrase: