In the following lesson, you can learn how to Returning Data Using SQL Output Parameter and execution process with an example.

If you specify the OUTPUT keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits. To save the value of the parameter is a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the procedure. For more information about what data types can be used as SQL output parameters.

Examples of SQL Output Parameter

The following example shows a procedure with an input and an output parameter. The @Name parameter would receive an input value specified by the calling program. The SELECT statement uses the value passed into the input parameter to obtain the correct ID value. The SELECT statement also assigns the value to the @ID output parameter, which returns the value to the calling program when the procedure exits.

How to Execute this Procedure

Returning Data Using a Return Code

A procedure can return an integer value called a return code to indicate the execution status of a procedure. You specify the return code for a procedure using the RETURN statement. As with OUTPUT parameters, you must save the return code in a variable when the procedure is executed in order to use the return code value in the calling program. For example, the assignment variable @result of data type int is used to store the return code from the procedure my_proc, such as:

Execute multiple SQL output Parameters

Returning Data Using SQL Output Parameter

The article was published on February 4, 2017 @ 8:09 AM

Leave a Comment