Stored Procedure returning entities and output parameter

If you have a stored procedure that returns a collection of entities and also an output parameter, you must iterate through the resultset before the output value is available on the client. Accessing the output parameter value before looping through the result would give you null value. Let’s walk through an example to see how we can correctly access an output parameter value from a stored procedure.

1.  Create Movies table with Title, Rating and Year columns as shown below.

2. Create a stored procedure that returns movies filtered by year. The stored procedure also returns average rating for those movies as an output parameter. Code below shows the stored procedure

3. Import the table and the stored procedure into an ADO.net Entity Data Model.

4. Open up the model browser by right clicking any where on the model surface and selecting model browser. Find the GetMovies stored procedure in the store section of the model browser. Right click the store procedure and select Add Function Import. Choose the return type to be Movie entity and click Ok. This creates a method on our derived object context that will allow us to call our stored procedure.

Now we are all ready to call our GetMovies method. The GetMovies method takes two parameters. First parameter is the year which is used in our stored procedure filter. Its of nullable integer type. Second parameter is an ObjectParameter which we will use to retrieve the averagerating output value from the stored procedure. Code below returns movies and average rating for those movies.

On the above code, we create an object parameter with the name of averagerating of decimal type. The value for this parameter is automatically set by the runtime once you have finished iterating through the resultset.

Figure below shows the output from the above code.