I’m trying to set standards for the usage of SQL stored procedures and could use some opinions about it. We are a VFP shop in the process of moving VFP data over to SQL. For the moment, we plan to continue to have middle and front ends remain in VFP.

One view point I hear is that we should simply have all access to SQL tables be performed through calls to SPs. That this allows for optimum performance and also allows the DBA to easily manage table structural changes because he can use SQL procedures to easily find dependencies upon any field. If SQL pass through was used, he would no longer be able to do this.

Another view though is to spread the SELECT, UPDATE, INSERT code as is appropriate throughout the application. Ie. INSERT and UPDATE code in the business objects, and SELECTs where ever needed, but primary for reporting. Basically, if what you need from SQL server can be accomplished in a single statement, why bother creating a stored procedure for each one? This approach is seen as having a huge developer productivity benefit, since the application has 100s of SELECT statements that would be unique to specific reports within the system.

The common agreements we have between these veiw points is that in the case of code that may be reused, they want it in one place. The first view advocating that spot be in SPs, the second view being in business objects.

Stored procedures are not required for security purposes to our tables. Security can be adequately managed in other ways. Performance issues are generally of the end to end through put nature and not in the sense of 1000s of people simultaneously running the code.

Please jump in here with your views and any pros and cons you see.

Thanks,
Anthony Letts