SQL Server Stored Procedures – Fundamentals

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_updateuser
Description:  Updates user information
Author:  Tom O’Neill
Modification Log: Change

Description                  Date         Changed By
Created 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=@email

WHERE usr_id=@usr_id

What’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=@login

Remember, 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.

Continues…

Leave a comment

Your email address will not be published.