Stored Procedure

[charon]

Just curious to know since the Stored Procedure is so effective, why I found that most of the database query sample code use normal SQL statement instead of Stored Procedure???

[raf]

Well, i can only speak for myself off course, but i don't use them because:
- embedded sql is more universal (though there are differences between db-formats;
- embedded sql makes more sense to me when i'm coding or debugging some code. Else you need to look up the procedures you created.
- with embedded sql, if i switch to another db, i now have only very limited modifications to make in my code (just the connection-include) and maybe, if necessary, a few commands that are not supported by the new db (but i always try to only use commands that could be though of as universal for the db's i use (MsAccess for almost-single user app's or MySQL for real production app's). If you'de use stored procedures and would like to switch to another db, it could mean you'd have a lot of modification or recoding to do.
- MySQL doesn't support Stored Procedures (should be implemented in version 5)
- i'm not really familliar with them

If you're sure that you'll stick with your db-format, then you should use stored procedures: will almost allways run faster, more secure (you can configure you're db to only allow modifications from stored procedures), cleaner code ... But being sure is something relative ...

[allida77]

Writing a sp can be a little more complicated then writing a regular sql statement. It would cut out some of the audience if a tutorial used a sp. And everything Raf said.

A little OT but here is a good link about sp performance:
http://www.sql-server-performance.com/stored_procedures.asp

[charon]

mm....it is true, I agree with your points. TQ.:))