1. Let's EXEC!
Now that you have created several stored procedures, let's see how we can put them to use.
2. Ways to EXECute
There are several ways to execute stored procedures. Sometimes there is no output parameter and you don't need to store the return value.
Other times you might need to store and evaluate the return value.
You need to anticipate if a stored procedure is designed to return an output parameter. If an output parameter is defined SQL Server will return an error if you don't assign an output parameter when executing.
What if you need to store both the output parameter and return value?
Even though stored procedures don't return table valued results sets, like functions can, you can still store a result set that is returned.
Let's take a look at examples for each of the scenarios listed.
3. EXEC in action
The first code section shows how to execute a stored procedure with the EXEC keyword followed by the SP name. If the stored procedure has input parameters, like the UPDATE SP shown, they need to be assigned a value.
If a stored procedure returns an output parameter a local variable should be declared to store the value. The variable data type should match what the stored procedure will return. The EXEC keyword is followed by the stored procedure name. The @DateParm input parameter value is assigned and then the Output parameter is assigned to the local @RideHrs variable. The output parameter assignment must be followed by the OUTPUT keyword.
Since we are declaring a local variable named @RideHrs to store the output parameter value, we can select it and view its result which is 77,733 in this case.
4. EXEC in action
Let's execute TripSummaryUpdate again, but this time we will store the return value. Remember the return value indicates if the stored procedure encountered an error or not. First, we declare a local integer variable named @ReturnValue, which is set to the result of the TripSummaryUpdate stored procedure.
A non-zero value indicates an error was encountered, so we can assume our update was successful when we select the ReturnValue variable and see its value is zero.
What if we need to store both the return value and the output parameter? First, we declare variables to store each. As before, the return value gets set to the result of the SP. The @RowCount parameter value gets assigned as the output parameter with the OUTPUT keyword.
When the ReturnValue and RowCount are selected, we see the SP finished without error and one row was affected by the delete action.
5. EXEC & store result set
You previously learned stored procedures don't behave like table valued functions. They don't return result sets in the same way and you can't execute them by SELECTing from them. However, you can return a result set from a stored procedure and store it.
Here we declare a table variable named @TripSummaryResultSet and define the columns. The column data types should align with the data types of the stored procedure's result set.
Then the INSERT INTO keyword is used, followed by the EXEC keyword, stored procedure name, and input parameter. This is how you store the result set returned from the stored procedure.
Finally you can see the table variable contents when we select from it.
6. Time to EXEC your SPs!
It's your turn to put your stored procedures to work!