SQLConfiguration SQLConfiguration


Stored Procedures  «Prev  Next»
Lesson 11Executing stored procedures
ObjectiveExecute 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:
  1. Separate each value with a comma.
  2. List each value in the same order as the parameters in the stored procedure.
  3. Specify the OUTPUT keyword for parameters that will pass back values.
  4. 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:
  1. A calling program must also specify the OUTPUT keyword for parameters that will pass back values.
  2. 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