First, let’s create the header information (like the author, change log, etc.) that should be a part of every stored procedure.
/*
Name: usp_adduser
Description: Adds a user
Author: Tom O’Neill
Modification Log: Change
Description Date Changed By
Created 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)