We are telling the database that we want to create a stored procedure that is called “usp_displayallusers” that is characterized by the code that follows. After the “AS” entry, you will simply enter SQL code as you would in a regularly query. For our first, we will use a SELECT statement:
SELECT * FROM USERLIST
Now, your stored procedure should look like this:
/*
Name: usp_displayallusers
Description: displays all records and columns in USERLIST table
Author: Tom O’Neill
Modification Log: Change
Description Date Changed By
Created procedure 7/15/2003 Tom O’Neill
*/
CREATE PROCEDURE usp_displayallusers
AS
SELECT * FROM USERLIST
Congratulations, you have written your first stored procedure. If you authored the procedure in a text editor, now would be a good time to copy it into the New Stored Procedure window in SQL Server. Once you have done so, click the “Check Syntax” box. This is a great troubleshooting tool for beginners and experts alike. When SQL Server tells you “Syntax check successful!”, you can click OK to save your stored procedure. To view the procedure, simply double-click usp_displayallusers in the Stored Procedures window. To run your stored procedure, open the Query Analyzer and type:
exec usp_displayallusers
Then, click the green “play” button to run the query. You will see that the procedure has run successfully.
It can be frustrating to start from scratch. Right now, you can think of all the things you want to accomplish with stored procedures; you just need to learn how! That will happen next. Let’s take a look at some more useful stored procedures.
More Sophisticated Stored Procedures
In 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.