Beware of the Cursor!! OR Not….?
I have read a lot of articles and tips online that say to stay away from cursors in SQL. I just wanted to show you an example of how I recently added a cursor to a custom SQL script that was written by someone else in order to add a feature that was requested.
I work with property appraisal data every day. This includes things like land and building value assessments. I had a coworker come to me with a script that was used to add a type of override assessment value to a specified account. This script contained a variable for the account number and they would have to manually change the number and run the script over and over to push in an override value for a list of accounts. So they asked me if I could make the script run for a list of accounts so they wouldn't have to execute it 70+ times to push in these override values.
First, I read the script and got an idea of what it was doing. Here’s a look at what I was working with:
Pretty straightforward! Some variables need declared, then the existing record is deleted and a new record is inserted into the table with the declared values. My coworker had a list of accounts that all needed the same value in the same year so the only thing they needed to change every time they executed the script was the account number. Keep in mind I still needed to keep this script at a level where they could execute it with little SQL knowledge. So here comes the cursor!
That’s it! I wrapped the entire script in a cursor so that whoever executes this can now provide one account number or an entire list of account numbers and the script will insert the override value into all of the accounts in the list. The cursor object is used to loop through the list of account numbers and execute the insert for each account. I barely modified the original script but was able to make the script now do exactly what was requested.
I hope this provided some helpful insight on when a cursor object can come in handy. Any feedback is appreciated! Thanks for reading!
