Stored Procedures For Beginners

It looks pretty long and complex, though we know from the process above that the stored procedure is not necessarily complex; it just contains a lot of data. If you have been working in a separate text editor, copy your stored procedure into the New Stored Procedure window in SQL Server, and check the syntax. The result should be a successful syntax check.

Now, we have a stored procedure that can accept external data. What do we do with it?  How do we get the data?  It’s not that hard; I promise. We’ll start with the "exec" statement we used when we wrote our first stored procedure. Remember?

exec usp_displayallusers

We have a new stored procedure to execute, so this time, the command will be:

exec usp_adduser

There is still the issue of how to get our data into the stored procedure. Otherwise, all those variables will be useless. To get data into our stored procedure, simply add the information (in single quotes ' ') after the execute statement.

exec usp_adduser ' '

Remember to pass as many parameters as you have variables, otherwise SQL Server will throw an error. Since we have ten variables, your execute statement should look like this:

exec usp_adduser ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' '

Next, let’s include the data that we will want to pass to usp_adduser. Your execute statement will look like:

exec usp_adduser 'dnelson', 'dean2003', 'Dean', 'Nelson', '200 Berkeley Street', ' ', 'Boston', 'MA', '02116', 'dnelson@test.com'

Running the query should be successful, and SQL Server will tell you that one row has been affected. Now, let’s try using input variables with some other query types.

Input Variables with SELECT and UPDATE Statements

Regardless of the type of SQL statement you use, variables work the same way. Look at the following stored procedure:

/*
Name:  usp_updateuser
Description:  Updates user information
Author:  Tom O’Neill
Modification Log: Change

Description                  Date         Changed By
Created procedure            7/15/2003    Tom O’Neill
*/

CREATE PROCEDURE usp_updateuser

@usr_id int,
@login varchar(20),
@pswd varchar(20),
@f_name varchar(25),
@l_name varchar(35),
@address_1 varchar(30),
@address_2 varchar(30),
@city varchar(30),
@state char(2),
@zipcode char(10),
@email varchar(50)

AS

UPDATE USERLIST

SET

login=@login,
pswd=@pswd,
f_name=@f_name,
l_name=@l_name,
address_1=@address_1,
address_2=@address_2,
city=@city,
state=@state,
zipcode=@zipcode,
email=@email

WHERE usr_id=@usr_id[yenisayfa]

What’s different about this stored procedure (compared to the INSERT stored procedure)?  Aside from the obvious fact that this is an UPDATE instead of an INSERT?  First, you should have noticed that we added another variable, @usr_id. This new variable has the datatype "int" because it is an integer field. Why did we have to do this?  In the INSERT stored procedure, we were creating a new record. Since usr_id is assigned by the system, we didn’t need to worry about it. Now we are updating an existing record. To ensure that we update the right record, we need to use the primary key as a filter. Notice that @usr_id shows up again in the WHERE clause, where we would normally have a value in quotes (like '1233').

The other difference is that we have included the variables in the SET clause. Instead of:

login='dnelson'

we have used:

login=@login

Remember, when you use variables, you do not have to use quotes.

The remaining SQL statement to address in this section is the SELECT statement.  We can pass data to a SELECT statement using variables as well. I’ll let you do this one yourself.

Exercise:  Pass Data to a SELECT Stored Procedure

Create a stored procedure that returns one record, based on the table’s primary key. Remember to:

1. Create the header record (commented)
   
    

2. Create the stored procedure name and declare variables
   
    

3. Create the rest of your stored procedure

When you are done, copy your stored procedure into the SQL Server New Stored Procedure window (if you are using a separate text editor), and check the syntax. Also, you may want to open the Query Analyzer and run the execute statement. I’ll provide both the stored procedure and execute statement (with sample data) below.

Answers

Stored Procedure:

/*
Name: usp_finduser
Description:  find a user
Author:  Tom O’Neill
Modification Log: Change

Description                  Date         Changed By
Created procedure            7/15/2003    Tom O’Neill
*/

CREATE PROCEDURE usp_finduser

@usr_id int

AS

SELECT * FROM USERLIST
WHERE usr_id=@usr_id

Execute Statement:

exec usp_finduser '1'

Did it work?  If not, keep trying!  You’ll get there.