More Sophisticated Stored Procedures - by tom o'neilIn this section, we are going to address a few new topics. In addition to writing SELECT queries, you are going to want to insert, update, and delete database records. Also, you will probably want to pass information from outside the query. Since inserts and updates require some sort of data input to be useful, our first topic will be variables. From there, we will use data stored in variables for inserts and updates.
Note: In this article, we will only address input variables (variables that pass data to the SQL statement in the stored procedure). There are various types of outputs and returns, and they can become quite complex. Since this article is an introduction, we will leave outputs for another time.
Input Variables
There are many reasons for wanting to pass data to a stored procedure, especially if your stored procedure is being called by a dynamic web page or other application. You may want to use a SELECT statement to pull information into the application for dynamic display. In this case, you would pass selection criteria to the stored procedure (for use in a WHERE clause). If you are inserting new records, you will need to get the data from somewhere. Updating existing records also involves simply getting the data. In both INSERT and UPDATE statements, it is necessary to pass data to the stored procedure. For INSERT, UPDATE, and SELECT statements (to name a few), you can pass the data to your stored procedure using variables.
Input variables are essentially "storage" for data that you want to pass to your stored procedure. Inside your stored procedure, you will declare variables at the top of the stored procedure. How does the data get there? The data is entered in the exec statement that you use to kick off the stored procedure. We’ll discuss that in more detail in a bit.
There are two types of variables that you can create in SQL Server stored procedures: Global and Local. Since this is for beginners, I don’t want to go crazy with too many options. We’ll stick to local variables for now. You can name a variable most anything you want, though it is best to stick with meaningful works and abbreviations. I also tend to avoid punctuation, though underscores ("_") are sometimes helpful. The only real requirement is that you begin your variable with the "@" symbol. Here are some examples:
@f_name
@fullname
@HomePhone
@ext
For every data element you want to pass, you will need to declare a variable. Declaring a variable is quite easy. You decide on a name and a datatype (integer, text, etc.), and indicate the name and datatype at the top of the procedure (below the "CREATE PROCEDURE" line). Let’s add a record to USERLIST. Remember the following:
"usr_id" is the primary key, and is system-generated. We won’t need to pass a value for it.
"login", "pswd", "l_name", and "email" are required fields. We will have to pass values for them.
First, let’s create the header information (like the author, change log, etc.) that should be a part of every stored procedure.
/*Name: usp_adduserDescription: Adds a userAuthor: Tom O’NeillModification Log: Change
Description Date Changed ByCreated procedure 7/15/2003 Tom O’Neill*/
Remember this?
CREATE PROCEDURE usp_adduser
/*We will put the variables in here, later*/
Add the "CREATE PROCEDURE" line, assigning the name "usp_adduser". Our next step is to remove the comments and declare our variables!
To start, let’s look at how our variables will fit. We will need to create a variable for every value we may need to pass. We may not pass a value to every field every time we run the stored procedure. But, we do need to address the possibility that over the life of the stored procedure, every data element may be used. The best way to address this issue is to create a variable for every column in USERLIST. To keep this example simple, we are also assuming that each of the columns can be NULL, and we will also be passing all of the variables to the stored procedure. If some of the columns cannot be NULL, or if not all of the columns will be affected, then the stored procedure and/or the exec statement have to be rewritten slightly. The list below shows the variable and the field with which it is associated.
@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
You have probably noticed that I gave the variables names that closely resemble the column names with which they are associated. This will make it easier for you to maintain the stored procedure in the future. Delete the comments about variables, and put your list of variables beneath the "CREATE PROCEDURE" line.
CREATE PROCEDURE usp_adduser
@login@pswd@f_name@l_name@address_1@address_2@city@state@zipcode@email
Next, add datatypes to each of the variables. The datatype assigned to the variable should match the datatype assigned to the corresponding column in the database. For any elements with the "char", "varchar", or "numeric" datatypes, you will need to put the maximum character length list in parentheses after the datatype. Separate all variables (except the last one), with a comma.
CREATE PROCEDURE usp_adduser
@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)
With that last keystroke, you have created your first set of variables. To finish "usp_adduser", we will have to figure out what we want the stored procedure to do, then add the appropriate code after the "AS" statement. This stored procedure will add a new record to the USERLIST table, so we should use an INSERT statement. The SQL should be:
INSERT INTO USERLIST (login, pswd, f_name, l_name, address_1, address_2, city, state, zipcode, email)
The INSERT clause is pretty standard. The VALUES clause is a bit more complex. If you have worked with databases, you are probably accustomed to seeing something like this:
VALUES ('dnelson', 'dean2003', 'Dean', 'Nelson', '200 Berkeley Street', '', 'Boston', 'MA', '02116',
'dnelson@test.com')
Since we are passing values from variables, it will look a bit different. Instead of putting the actual values in the VALUES clause, we’ll just put the variables. You won’t need to use quotes.
VALUES (@login, @pswd, @f_name, @l_name, @address_1, @address_2, @city, @state, @zipcode, @email)
What does the entire stored procedure look like? Let’s pull it all together.
/*Name: usp_adduserDescription: Add new logins.Author: Tom O’NeillModification Log: Change
Description Date Changed ByCreated procedure 7/15/2003 Tom O’Neill*/
CREATE PROCEDURE usp_adduser
@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
INSERT INTO USERLIST (login, pswd, f_name, l_name, address_1, address_2, city, state, zipcode, email)
VALUES (@login, @pswd, @f_name, @l_name, @address_1, @address_2, @city, @state, @zipcode, @email)
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_updateuserDescription: Updates user informationAuthor: Tom O’NeillModification Log: Change
Description Date Changed ByCreated 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=@emailWHERE
usr_id=@usr_idWhat’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=@loginRemember, 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:
Create the header record (commented)
Create the stored procedure name and declare variables
Create the rest of your stored procedureWhen 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_finduserDescription: find a userAuthor: Tom O’NeillModification Log: Change
Description Date Changed ByCreated procedure 7/15/2003 Tom O’Neill*/
CREATE PROCEDURE usp_finduser
@usr_id int
AS
SELECT * FROM USERLISTWHERE
usr_id=@usr_idExecute Statement:
exec usp_finduser '1'
Did it work? If not, keep trying! You’ll get there.
In Closing
This has been a pretty aggressive lesson. You showed up somewhat familiar with databases, but probably knowing nothing about stored procedures (unless you are a database guru who read my article so you could viciously critique it later!). We have gone from defining stored procedures to writing them independently. That is great! Stored procedures are an excellent way to insulate your programming logic from the threat of technology migrations in the future. They are useful, make for efficient application development, and are easy to maintain. Using the information and exercises above, you should be on your way to creating stored procedures to support any database-related endeavor
