Stored Procedures For Beginners

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_adduser
Description:  Add new logins.
Author:  Tom O’Neill
Modification Log: Change

Description                  Date         Changed By
Created 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)