Friday, August 16, 2013

REFACTOR & SQL Code Reuse

A quick post about some updates I was working on today. I will not be posting any snippets with this post, it will be more of a challenge to think of how you could refactor your own code to get away from repeated code.

I have a few SSRS reports that use a lot of the same temporary tables and variables but they are simply copied and pasted through the SQL code.

This. Drives. Me. Nuts.

I feel almost guaranteed to forget to update some variable somewhere if it exists multiple places so today I decided to take some study time and refactor this code to get away from this bad practice and I feel it came out very successful.

To make this work I used a combination of stored procedures and function calls.

I have four reports that I really wanted to update and they are in groups of twos. They provide data on different areas but one of the sets provides a week view and the other a month view. The four reports have temporary tables that share a similar structure with different offshoots of columns.

To improve this I made a function that built and returned the base temporary tables and then other respective functions that called the first function, added the other needed columns for each report, and returned the table to the calling stored procedure that then fills the table with the data.

The other update ended up saving me quite a few extra lines of code. This function returned a table populated with data regarding time billable but I was able to also add a bit parameter to the function to specify if I wanted to return only billed time or non-billed time and then add a SQL IF statement off the bit to return the needed data. This allowed me to use the same function call to get whichever data needed in multiple places.

If you have any tips and tricks on SQL code reuse I'd love to hear them. I find that SQL code reuse can be a bit tricky so any tips are well appreciated by not only me but the community as well, I'm sure.

No comments:

Post a Comment