Database-Design.org presents

Database Design Question #22

If you use stored procedures, how do you decide to use them or not use them? What are your alternatives?

Jules Bohanon, Lakeland, Florida: SPs are indispensible when writing an Access front end to a SQL back-end, especially when working with tables of 1 million+ records.

Assem Bayahi, Mourouj 1, Tunisia: Well, lately, another client asked me, based on GPS data, to list all the trips taking in consideration the car speed and other conditions: well, because I have to extract « parallel » data from one table (list of start - end), I directly decided to use a stored procedures. For me it's simple, if I can't use SQL (need for a loop or some complicated calculations) then a procedure is the solution.

Md. Obaidul Haque Sarker, Dhaka, Bangladesh: Stored procedure is good when you need to do some complex queries and calculation in database. All queries are embedded in a single stored procedure which is called by the application in once. But if you execute each query in separately, it is very costly. It degrades the performance too.
If you want to execute single SQL statement in application, you don’t need to write stored procedure.

Ven Grollmus, George Town, Tasmania, Australia: The decision on stored procedures is determined on what they need to return, the complexity of the data being viewed, and the performance needed. The other alternatives are Views, and you need to look at performance as well with these.

Bruce Bray, Phoenix, Arizona: I use Stored Procedures and UDFs a lot. I prefer to use stored procedures for action type sequences of code. For example, user updates data in a form, and data needs to be updated in several places at once. I find this to be a particular great use of Stored Procedures. Or when doing batch jobs.
I know some people like to return recordsets using them. And I have done that. But I actually prefer using UDFs for this because you can just call it as a table passing a parmeter and then further filter it using a SQL Statement. I use that a lot for reports.

Yuriy Sultanaev, Ufa, Russia: Rejection of the stored procedures only in lightly loaded projects.
Stored procedures smear out the the application code so that people come up with ORM that operate poorly...

Temitayo Ilori, Berea, Ohio: If vendor can change, don’t use them. Alternatives involves the inclusion or exclusion of parameters.

Grace Elaiza Seballos, Davao City, Philippines: I do not use them. The reason for this is that, I use the 3-tier architecture approach. The data layer, the business logic layer, and the presentation layer are always separated from each other. As I have mentioned before, my skills are honed with custom-based and tailor-fit systems. This means, if I have completed an Inventory System for a client that is using a Oracle database, and then, sometime down the road, the user want to change the database int My SQL. Then I do not have to program the business logic and presentation layer anymore. All I have to do is to configure my data layer for My SQL. And then my client wants a mobile version of the inventory system, then he/she just needs to find a mobile developer. No need to reprogram the business logic or explain to the mobile programmer about the database structure. The Data Layer always communicate with Business Logic Layer. And the business logic layer is responsible to send data from the presentation layer (mobile) and receive data from the presentation layer. This data is then processed by the Business Logic before it is sent to the Data Layer. Then the data layer will just save it into the database (wether commit or uncommit depending on errors encountered during RDBMS interaction). This actually is the solution in order to have a cost-effective system. The mobile programmer will just create the input UI, and send those input to the business logic. Business Logic will do all calculations. Mobile programmer doesn't even need to know how the inventory is to be saved or computed.