Stored Procedure V/S direct SQL codes

cijojosev · New User Joined: 16 Dec 2005 Posts: 4 Location: Mumbai

hi friends,

can anyone specify that what all we have to consider while deciding on whether we should use a Stored Procedure or a direct SQL code to do a database operation

iknow · Posted: Mon Dec 19, 2005 8:57 pm

Hi There,

To your question one simple solution is that if your problem can be solved through SQL queries go ahead with it. You can make use of stored procedures or SQL queries, it all depends on the requirement of your project.

If you implement stored procedure concept in your code then you must be able to analyze the impact of the code how it is getting populated.

Hope I reached you.

Correct me if I am wrong.

DavidatK · Posted: Mon Dec 19, 2005 9:20 pm

Good morning, Well at least on this side of the world,

There are many considerations using a stored procedure vs. direct SQL.

There are two types of stored procedures, SQL and external (i.e. COBOL Program).

In our shop we only create COCOL Stored Procedures, so I don?t have much experience with the SQL stored procedures.

Some of the benefits of using a COBOL stored procedure, or other language that supports DB2, is that the logic can be quite complex. It is, in fact, a subroutine that can contain all the functionality of any other subroutine you might write. I wrote one about a year ago that traverses 18 tables. In this case, as with all public subroutines, anyone that uses the subroutine receives the same answer because the logic is the same, nobody is trying to re-invent the wheel, and if the logic requires updating, everyone gets the update.

On the down side of DB2 Stored Procedures, they are very expensive on resources and should be called on a limited basis. The stored procedure I mentioned above was developed for an on-line application, where an additional .1 - .25 seconds for an on-line application is a small price. In a batch program that tried calling it about 8 million times as a mass update would have run for days. You do the math.. I am currently re-writing the stored procedure as a mass update program using several temporary tables and joining the 18 tables above in a different way that will take less than 15 min to run.

So, the answer is not easy. If the SQL is simple, and going to be called many times, I?d probably code it directly in the program. The more complex the logic, the Stored Procedure becomes a better choice, but called on a limited basis.