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)
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.