Lesson 10	Using Output Parameters
Objective	Practice using output parameters.

Using Output Parameters in Stored Procedures

In SQL Server, there are primarily two methods for returning data from a stored procedure to a calling program: using the `RETURN` keyword and utilizing output parameters. Each method serves a specific purpose and has its unique implementation and use case.

Using the `RETURN` Keyword:
- Purpose: The `RETURN` keyword is primarily used to indicate the success or failure of the stored procedure. It is typically used to return an integer value, often representing an error code or a status code.
- Implementation: At the end of the procedure, you can use the `RETURN` statement followed by an integer value. This value is then retrieved by the calling program.
- Limitations: The `RETURN` keyword can only return integer values. It's not suitable for returning complex data like strings, tables, or multiple values.
Example:
```
	 CREATE PROCEDURE SampleProcedure
     AS
     BEGIN
         -- Procedure logic here
         -- If successful, return 0, otherwise return an error code
         RETURN 0
     END
```
In the calling program, the returned value can be captured and used to determine the next course of action based on the success or failure of the procedure.
Using a Variable as an Output Parameter:
- Purpose: Output parameters allow a stored procedure to pass data back to the calling program. They are more versatile than the `RETURN` keyword, as they can return any data type supported by SQL Server, including complex data types.
- Implementation: In the stored procedure, you define one or more parameters with the `OUTPUT` keyword. You then assign values to these parameters within the procedure. The calling program must declare corresponding variables to receive these values.
- Flexibility: Output parameters can return multiple values and support a wide range of data types. They are ideal for returning detailed data from the procedure.
Example:
```
 CREATE PROCEDURE SampleProcedure
	 @Result INT OUTPUT
 AS
 BEGIN
	 -- Procedure logic here
	 -- Set the value of the output parameter
	 SET @Result = [Calculated Value]
 END
 
```
In the calling program, you would declare a variable to receive the output parameter's value and access it after the procedure call.

In summary, the `RETURN` keyword in SQL Server is best used for sending simple status or error codes from a stored procedure, while output parameters offer a more flexible and robust way to return complex data and multiple values. The choice between the two methods depends on the specific requirements of the calling application and the nature of the data being returned.

There are two ways of returning data from a stored procedure to the program that calls or executes the stored procedure:

A return value indicates the value of the stored procedure as a whole. RETURN is discussed in the previous lesson.
Output parameters are variables that pass data into a stored procedure for the purpose of the stored procedure modifying the values and passing them back to the calling program.

Specifying that a variable is used for returning values is as simple as specifying the OUTPUT keyword, like this:

CREATE PROCEDURE usp_GetLastName @EmployeeID int OUTPUT
AS
SELECT LastName
FROM employees 
WHERE EmployeeID = @EmployeeID

After you specify an output parameter, there is nothing else to do inside the stored procedure.
The rest is done in the program that calls the stored procedure, as you will see in the next lesson.

Multiple Parameters

You can specify multiple parameters as being output parameters.

In the next lesson, you will learn how to execute your stored procedures.

Using Output Parameters - Exercise

Click the Exercise link below to practice creating stored procedures with output parameters.
Using Output Parameters - Exercise