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:
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
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:
we have used:
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.