When building any application that has data entry you will quickly require reports. These reports can come in a variety of formats. They may be in a .csv, .pdf, .rdf, Reporting services, Crystal Reports and a multitude of other options. However; when generating a Crystal Report or a Reporting Service Report you can actually link tables together, prompt for parameters and sorting records all within the designer screen. This might work when you know that your requirements will never change, however; this is rarely the case. If you build all of this in Crystal Reports and then need to change the tables or parameters it is very challenging to accomplish without spending a lot of time. If you always get into a habit of creating a stored procedure for each report than you can pass the parameters into the stored procedure and take advantage of several key benefits.
1. If a field name or table changes and you make it in the stored procedure then you do not have to touch the report definition. For example; take the following query;
Select first_name as first_name, last_name as last_name, middle_name as middle_name, home_phone_num as phone_num From Employee Order by last_name
Now build a report off of this definition. Then client comes and says that they want to use the business phone number instead. If you did this in the designer you would have to open it up and make the changes everywhere it says home_phone_num. However; if you build a “middle” layer then all you do is change the stored procedure like so…..
Select first_name as first_name, last_name as last_name, middle_name as middle_name, Business_phone_num as phone_num From Employee Order by last_name
2. You allow SQL to manage the where and order by requirements which will always be more efficient than doing it in the reporting platform.
3. If you have a several linked tables than doing it in a stored Procedure will allow you to debug it and reuse the data when needed. (Instead of having to build a report to see the data)
I hope that I have convinced you to consider using a “middle” layer (Stored Procedure) for all of your reporting needs. If you think of other benefits than send them to me and I will add them to the list.
Looking for quality web hosting? Look no further than Arvixe Web Hosting!