Hi Bonnie,

I still prefer the application be separate from the database. Not only does it make the app more portable, I feel like it makes the app easier to maintain and debug. For example, in using SPs the applcation must still call the SP in the database back-end in order to execute it. If something is wrong, the app's code calling the SP must be examined. If it appear OK, the SP in the database itself must be tested.

When using SPT, the debugger could be set on, (e.g. set step on), and I could walk through The SPT one line at a time to isolate any problems. Also, because the SPT are part of the application, it makes it earier to read the program and know exactly what is being done. It is much less evident what the code is doing when viewing only the call to a SP in an app. Things can become messy if SPs that are used by an application are mixed in with SPs create and used by others outside the application. It becomes difficult to know what belongs to the app and what belongs to IT, markeiting, Resource Management, etc. This introduces the possibility that someone who is not familiar with the app accidentially changes or deletes the SPs in the back-end server. There have been cases were IT has deleted SPs that are part of an app thinking they were obsolete and no longer being used. If SPs used by the app were altered or deleted, the app would malfunction causing delays until the problem was isolated and corrected. I would feel more secure knowing the total app was in a project under my control, and only individual working on the app had access to it.

The app could have a Special Procedure in which all its SQLexec() SPTs were housed. Each form/program could execute the appropriate SPTs in the Special Procedure. If the SPTs needed changing, it could be done once in the Special Procedure and all forms/programs calling the Special Procedure SPTs would inherit the changes. This would tend to isolate SQL needed by the application eliminating any confusion about what was used by the app, what was used outside the app, and what was used by both.

I know SPs are wonderful. MSSQL makes it easy to create SQL and test them, so they can be polished to do exactly what I want them to do. However, if SQL applies to a front-end app, I would prefer to cut and paste the SQL into a Special Procedure in the app especially designed to hold STPs of the app, as opposed to converting them to SPs that becomes mixed in with 1000s of other SPs being used by different people, departments, etc. It is to easy in this kind of confusion to make mistakes like duplcating SPs already created by other, or accidentially deleting needed SPs.