Thursday, May 3, 2012

Rethinking the Use of Stored Procedures

As I mentioned in a previous post, I was absent from Delphi programming for about 7 years. During that time I worked 50-60 hours a week on custom database driven websites using ASP and Microsoft SQL Server. I estimate that this resulted in 200 (plus/minus) websites.

Programming for the web taught me about statelessness, bandwith conservation, and the biggie, SQL Injection. Being the lead programmer I developed standards for the use of stored procedures in everything we did. I learned a lot, made my fair share of mistakes, and developed a deep admiration for stored procedures.

The focus became take a request from a webpage, go to the server, do as much on the server as possible while you are there, and return the results to the webpage. Works great.

So, now I'm back into Delphi and rewriting a desktop application that hasn't been updated since 2004. The jump from D5E to D2010 in itself, has been a challenge. I'm also going from a non-database application to a databse application. The database I have chosen to use is ElevateDB.

One of the reasons I chose ElevateDB was it offers stored procedures. I love stored procedures, they are like black boxes. As long as the interface doesn't change they are good-to-go and offer awesome power.

So, I'm taking all this thinking I developed throughout those web years and applying it straight to Delphi and the application rewrite I'm doing. I know that a stand alone desktop application doesn't have to worry about statelessness, or bandwith conservation, and for the most part SQL injection. But it's hard to re-learn to think... until the rubber meets the road.

The rubber met the road when I decided to build my first report using ReportBuilder. I've never truely had a reporting tool before... even in my web days I built everything from scratch. So, I took the amortization schedule I built as an ElevateDB stored procedure and made that my first report. Everything is going well, the report looks professional, I'm having fun.

Then I start thinking... wow it wouldn't be nice to add a a percentage of payment next to the principal column. Wouldn't it be nice to show the percentage of interest also. And it would be totally awesome to bunch them by the year the payment is due. So, I decide to add two calculated fields to my report, no big deal. PrinPct is simply Payment divided by Principal.

Then I realized...
  • You can't add a calculated value from a stored procedure column
  • You can't do a group by based on the the year of the payment
  • Wow, building a report based on the result set of a stored procedure is not the best approach
"My first thought was, great ReportBuilder doesn't let me do what I want to do. This sucks."
Then I realized...
"It's not ReportBuilder, it's me. My thinking is wrong. Stored procedures are not meant to be reported on... they are meant to do the heavy lifting that makes the data easy to report against"
I'm glad that this happened to me early on in the process. I would have been quite upset had I created a total database with 30-40 stored procedures all built on the assumption that they could be used as datasets for reporting.

Back to the drawing board... another lesson learned.

Semper Fi, Gunny Mike