Using Cursors

Using Cursors

Details

You will learn

This solution leverages a cursor for looping over the rows and calculating the sum and using UNION ALL for building the result set to solve the exercise. It is the slowest running solution out of the three shown possibilities.Please note - This tutorial is based on SPS11

Step 1: Create new procedure

Use what you have learned and create another procedure called calculate_cumulative_sum_of_delivered_products in the procedures folder.

Be sure to change the namespace from Undefined to dev602.procedures. Enter the input and output parameters as shown

Step 2: Add DECLARE statements

Enter the following DECLARE statements

Step 3: Add a loop

Enter a FOR loop which will iterate over the input parameter table and perform the calculation and finally update the output table parameter.

Step 4: Check code and save

The completed code should look very similar to the following. If you do not wish to type this code, you can reference the solution web page at http://<hostname>:51013/workshop/admin/ui/exerciseMaster/?workshop=dev602&sub=ex2_20

Return to the procedure called get_product_by_filter and remove the last SELECT statement.

Step 6: Call the procedure

Insert a call to the procedure called calculate_cumulative_sum_of_delivered_products and pass the aggregated_filtered_items as the input parameter and set products as the output parameter as shown

Step 7: Add SELECT statement

Finally, add a SELECT statement at the end to assign the sorted results to the output parameter.

Step 8: Check complete code

The completed code should look very similar to this following. If you do not wish to type this code, you can reference the solution web page at http://<hostname>:51013/workshop/admin/ui/exerciseMaster/?workshop=dev602&sub=ex2_21