Assignment 6B – Cursors
Objectives
Use a cursor in a stored procedure
One of the most useful tools in stored procedures is cursors. You do need t know how to create them and iterate through them in a stored procedure.
Assignment
This is all about learning to use one of the primary tools that are often used in stored procedures – cursors. Of the hundreds of articles on Cursors – this is what I consider the most simple straightforward explanation – https://www.essentialsql.com/database-cursor/ . You will need to know what they are and how to use them. The good news is they really are not that complex.
Default Assignment
Please note that any example that you come up with that shows (1) creating a cursor, (2) iterating using a cursor, (3) row by row operations using a cursor – will satisfy this assignment. I am providing an example that would require a cursor so that if you do NOT have some example to use, you at least have something for the assignment.
You will be creating a simple stored procedure that uses one or more Cursors – name the stored procedure sp_DisplayCollege. You may develop multiple stored procedures to achieve this. You are going to use the data from COP4709 Assignment 2B – Import and Analyze Data
The stored procedure should accept a single argument – the name of a College, it will need to find all Colleges that match the name (including partial match).
For each College that matches the criteria – we are going to prepare a data report. This report will be a comparison of the college on each of these fields as compared to the average of the value of the field.
Your output should look something like this (does not have to be this format exactly -just needs to have the data).
Average is simply an average of all the values of the field AVG(CUML_DEBT_P90). The weighted average takes into account that some schools have more students than others – and thus takes into account the size of the school SUM(CUML_DEBT_P90*CUML_DEBT_N)/SUM(CUML_DEBT_N).
College: Alabama A&M University
| Statistic | Institution Value | Average Value | Weighted Average |
| Cumulative Debt at 10% | $4750 | $3530 | $2816 |
| Cumulative Debt at 25% | $9500 | $6243 | $5990 |
| Cumulative Debt at 75% | $37,900 | $18,542 | $27,976 |
| Cumulative Debt at 90% | $4750 | $25,146 | $39,153 |
| Sample Size for Statistic | 2497 | 5,629,495 | 5,629,495 |
Repeat for all matching colleges (that is where the cursor is important).
You will turn in the text of the stored procedure and a screen capture of the execution of this with results.
note: You may demonstrate your knowledge of the use of cursors through stored procedures that utilize stored procedures other than this specific example. You do NOT need to use this specific example, it was just provided as possible use of a Cursor in a Stored Procedure.
This will be turned in as a report: Problem, Solution, Results, Code. If you are unsure as to the format I have a discussion board for you to ask.
Information
The video and weekly reading on cursors should be sufficient for this assignment. I recommend writing a series of stored procedures and functions to achieve the report. First I would create a stored procedure that takes the input (Name of College) and creates a cursor from this. I would then call out to multiple stored procedures and stored functions to do the queries associated with the report. This will allow you to divide and conquer each of the pieces of the report.
Estimated Completion Time
5 hours to cover cursors and write all the necessary stored procedures.
Supporting Lectures
Topic – Stored Procedures Part 2
Questions and Answers
Q and A – Tackling the Cumulative Debt Cursor Problem
External Resources
DECLARE Cursor Syntax and Example – https://msdn.microsoft.com/en-us/library/ms180169.aspx
How Cursors Work – good easy to read article on Cursors – http://searchsqlserver.techtarget.com/feature/Part-1-How-cursors-work
Youtube Video (from WiseOwl) on Cursors – https://www.youtube.com/watch?v=RHRjLd0bEaQ
Grading Criteria
Demonstration of a working stored procedure using a cursor by submitting the code and the results are sufficient for completion of this assignment.