Doing away with stored procedures.

rxelizondo posted on Thursday, January 11, 2007

For some time now, I have been contemplating the idea of getting rid of the stored procedures and do all the data modification by accessing the tables directly.

I do realize that stored procedures have some major advantages, the biggest advantage that I can think of is that with stored procedures, a database administrator is able to control what can be done by each stored procedure call and this can prevent mistakes or abuse from happening. If the database administrator gave direct table access permission very bad things could occur.

But is there really a point of creating stored procedures for this purpose when the same person that is creating the database is the same as the one creating the code that modifies the data? Wouldn’t it be just a lot easier to make the table calls directly?

I do realize that the performance of the query will be better if the query was compiled in a stored procedure but assuming that the speed issue is not a critical thing, wouldn’t it just make more since to make direct data table calls?

You feedback is greatly appreciated.

Thanks.

DansDreams replied on Thursday, January 11, 2007

rxelizondo:

I do realize that the performance of the query will be better if the query was compiled in a stored procedure but assuming that the speed issue is not a critical thing, wouldn’t it just make more since to make direct data table calls?

You feedback is greatly appreciated.

Thanks.

See the other thread. The compiling performance thing is purely mythical, but over there we're discussing the very point you raise.

Bowman74 replied on Thursday, January 11, 2007

Since about 90% of my BO code code and associated stored procs are generated it really wouldn't be much easier for me from a development standpoint so I always generate stored procs. For me personally it is easier to see the clean SQL in a stored proc on the rare occasion when I need to write/debug SQL than looking at a bunch of concatenated strings in a dataportal method. I sometimes find the need to do complicated joins in non-generated SQL and I prefer to do that in Enterprise Manager.

Your preferences may vary of course. But most of the setting up of BOs and data access is pure drudgery and very simple; a prime target for generation. If you are not generating now I'd look into it. Once your generating it is no harder to generate stored procs than it is to generate dynamic SQL.

Thanks,

Kevin

ward0093 replied on Thursday, January 11, 2007

I have to agree with Kevin... I think maintanance of your SPs is just a little bit more... but it make for much cleaner code than a bunch of strings... if for no other reason than that...

I only use SPs in all my apps

ward0093

ajj3085 replied on Thursday, January 11, 2007

Personally I think its a bad idea. You not only have to think about developers making mistakes, but also your users. Its not very far fetched that an end user can link to a table through Access, for example, and screw up the data.

Also, one good thing about procs is that having and using them means you can easily add row level auditing, without changing your code. Row level auditing is one of those things I think are best done via these kinds of procs.

I'm not sure what kind of maintance problems you're having; my procs and views are generated by a tool I have, and outside of tweaking permissions now and then, I don't ever really have to touch them..

Andy

pelinville replied on Thursday, January 11, 2007

This turns out to be more a religious decision in many cases.

As long as you are aware of the pros and cons of each method then I suggest doing a project with the method you are curious about and see how it goes. Create some test cases where db schemas have to changed and see how easy/hard each method is for you.

Then just do what works best for you.

Personally I do not allow any SQL strings, even stored procedure names, anywhere in my BO's. And personally I avoid creating stored procedures until I have to.

But that is just me. (Some think I am either crazy or incompetent or both.)