| Lesson 11 | Executing stored procedures |
| Objective | Execute your stored procedure. |
Executing Stored Procedures
Executing a stored procedure is easy. You can use either a custom-made program that executes queries or the SQL Server Query Analyzer.
Using Query Analyzer
To execute, run, or call a stored procedure from Query Analyzer, simply specify the EXECUTE keyword, followed by the name of the stored procedure, like this:
EXECUTE [stored procedure]
Entering parameters
If the stored procedure requires parameters, you must specify their values. Because
usp_GetLastName requires two parameters, an error would occur with the above statement. When you are specifying parameter values, the following rules apply:
- Separate each value with a comma.
- List each value in the same order as the parameters in the stored procedure.
- Specify the
OUTPUT keyword for parameters that will pass back values.
- Use a variable, not a literal value, if a value is to be passed back. This is because a variable can have a value reassigned to it, but a
literal value, such as
5, cannot.
Case study: the usp_GetLastName stored procedure
Throughout this module, you have created and modified the usp_GetLastName procedure. In case you have not reviewed all the lessons and exercises pertaining to this procedure, here’s a quick recap:
The usp_GetLastName stored procedure:
| Attribute | Description |
| Name | usp_GetLastName |
| Type | User defined |
| Purpose | Determines an employee’s last name |
| Parameters | EmployeeID EmployeeStatus (data type is int) |
| Code used to define stored procedure | CREATE PROCEDURE usp_GetLastName @EmployeeID int, @EmployeeStatus int OUTPUT
AS SELECT LastName
FROM employees
WHERE EmployeeID = @EmployeeID
|
The following statement executes the usp_GetLastName stored procedure and specifies an EmployeeID value of 101 and an EmployeeStatus value of 5:
EXECUTE usp_GetLastName 101, 5
Although this statement is valid, it will not work in this example because the value for the @EmployeeStatus
parmeter cannot be used as an output parameter as called.
This is because:
- A calling program must also specify the
OUTPUT keyword for parameters that will pass back values.
- A variable (not a literal value) must be used if a value is to be passed back. This is because a variable can have a value reassigned to it, but a literal value, such as
5, cannot.
The following code will successfully pass back a value from the stored procedure:
DECLARE @TempStatus int
SET @TempStatus = 5
EXECUTE usp_GetLastName 101, @TempStatus OUTPUT
