Recycle + Reuse. Even Your Stored Procedures.
Published: January 14, 2014
Recycle + Reuse. Even Your Stored Procedures
Recently we needed to generate an ad hoc report for some financial data. The report needed to show what had been reimbursed to a provider from a funding source in the year-to-date and be compared against the approved budget, which may or may not have been amended. The budget is specific to the fiscal year, and so the year-to-date is defined by a fiscal year variable (and there’s not always 12 months in a fiscal year). Specifically we were looking for instances where the remaining balance is negative, meaning essentially that either the provider had been reimbursed too much, or the budget had been amended to less than the original budget resulting in a negative budget amount. Sounds easy enough, right?
With an unlimited amount of time to produce this report, I would have started writing queries from scratch. I’m very familiar with the data structure and already knew most of the tables involved. However in this case, as it usually is, I didn’t have an unlimited amount of time, and in fact had very little time. As it turns out, the calculations were fairly complex and required multiple steps to generate in the production system, so throwing together a quick and dirty statement (that matches the production algorithm) wasn’t looking promising. I wasn’t without a starting point however; I could get the numbers I needed for one provider at a time. Simple I thought, I’ll just remove some conditions in my WHERE clause, add in the few extra fields I need so this makes sense as a data set, and off I’ll go.
Not so fast. A red flag probably went up in your head too. When there are multiple steps to generate in a limited scope that usually means that some variables are involved, and that was definitely the case here.
There were many. So then what? My first thought was to create temp tables to store a set of values that were previously held in variables, but then I realized I had an even easier option.
Cursors – A Time Saving Secret for Reports
We don’t usually like to use cursors because they get a bad rap on performance (and some developers have abused them when set based operations were a better solution) but today a cursor saved a bunch of time, maybe even saved the day -- literally. Since this was an ad hoc request, the focus was on speed of delivery, not execution time.
By looping through my provider records in a cursor, I was able to call out to the existing stored procedure and insert the results into a table variable. This got all the information I needed with only a few minor alterations to the WHERE filter. This result got pasted into an Excel spreadsheet, and there’s still time for an afternoon trip to Starbucks. Which is awesome, because I have a rewards code for a free caramel Frappuccino that expires soon. Consider recycling and reusing the next time you have limited time and existing stored procedures!