From a Fox developer's perspective, a place for stuff about SQL Server Stored Procedures
Reasons to consider using back-end Stored Procedures
- Multiple SQL Statements can be combined into a single procedure.
- Stored procedures are only parsed once.
Actually, some SP's will be recompiled every time they are run. For example if you access an object that doesn't exist at original compile time such as a temp table. There are some other things that will cause recompile on execute to happen too.
Post SQL Server7, this can also be true for RVs and parameterised SPT
- Users can be granted different permissions on stored procedures than the permissions on the underlying tables.
- Stored procedures can encapsulate business rules.
Is that good?
- Stored procedures can reduce network use.
Can also increase network use in non-"batched append" applications
- Stored procedures can return record sets that are sorted views.
- Stored procedures can return record sets that do not represent any physical table.
- Stored procedures can improve the performance of server intensive work.
- Stored Procedures can access features that exist only on the database server
This is true of SQL that is built on the client which uses back end specific features.
- Security is enhanced because the only thing the user needs are Exec privledges on the SP. What the SP actually does is controlled by you. And, with SP's, you can use functions like app_name() to control which apps can execute the proc.
Using an approle or a single secure login for your application, your data can be just as secure, since the users don't know the approle or login password, they can't use the SELECT priviledges anyway.
This ignores the "reality" that 75% of recorded data misuse is caused by IT staff- who simply bypass the mighty SP. Security is more a "meatware" than a technical matter, a business as much as a technical response is needed. And this ignores the obvious that the IT staff can call the SP manually to perform misuse.
- Correctly implemented, Stored Procedures can act as an API to the database. Thus you can insulate the application layer from changes in the database layer. This can also be done with a Back End View Layer.
Stored Procedure downsides
- Back-end data structure changes ripple through the Stored Procedures.
Not necessarily. Just because a structure change is made, it does not neccesarily follow that those changes have to be relfected in the sp.
No? Try to modify a CHAR(1) to VARCHAR(10) and see if you don't get a Truncate Error when you try to select that field into a variable declared as char(1) in the SP! Try to add a field to a table or modify the order of the field list, and see if your INSERT without a field list won't complain.
Anybody who employees inserts without a field list is avoiding a best practice. As for structure changes, if an error in an SP results, at least you can go back to the SP and fix it. With a RV, you will be prevented from opening the view. That is the big difference - and one that you fail to recognize...
...because it isn't correct. If you are still using the VD rather than a better alternative, you are not "prevented from opening the view", you just get an error message. Against SQL Server all the field and key settings are retained, all you need to do is reset the update option. Not hard. Certainly easier than picking through SP insert clauses.
And why would I need to do that? In the example you provided, you were talking about the changed field being one referenced by a declared variable. Meaning I just change the variable declaration at the top of the SP and voila. If the altered field is not referenced by a declared variable, and is not involved in a calculation that could possibly be invalid if the type is changed, no changes are necessary in the SP.
If you simply change a field you *won't* see an error using the View designer which is what you asserted. You just need to open and close the view. The VD throws errors when you add/delete fields. Are you saying you won't need to pick through SP insert clauses in that case? What about field value parameters passed to the SP?
- Changes in cursors required by the application layers need to be done on the back-end. The SP must exist before you can use a cursor and develop with it. This can cause development delays. See also Schedule Risk and Schedule Risk - Development Environment.
What exactly does this mean?
- T-SQL, which is a set-oriented language, makes some VFP cake-walks difficult to engineer.
Actually, T-SQL is capable of row operations like VFP; it is not a set-oriented language only. Granted, T-SQL, as far as language features are concerned, is not on par with VFP. For example, where it would take VFP a line or two of code, it might 5-10 lines of code in T-SQL. This metric of course is not an absolute. Still, T-SQL is not as effecient as VFP.
Regardless of whether it takes more code to implement a solution in T-SQL, that often is not the issue that drives the bus on whether the backend is the proper place for the code. However, T-SQL, from a SQL Language standpoint, is much more advanced than VFP.
- Stored Procedures are, of course, in the back-end data layer. That's not necessarily where application-layer code should live.
Logic that is responsible for modifying data or grabbing result sets should be in the data layer. This way, full data/program independence can be achieved
- For all non-trivial applications, the developer is dependent on the cooperation, coordination, and execution of a knowledgeable back-end DBA.
Not neccessarily. Often, the developer can act as a DBA. However, this is being couched as a disadvantage when DBA's are actually a beneficial resource to have.
- Using SPs means your customer/s have to use the Backend/s for which you develop the SP
This does not matter for everybody, but for those whose large customers may have corporate database standards, it matters a lot. One would not want to lose a million-dollar deal because the competitor agrees to work with Sybase but we only have SSPs for Oracle.
See also Stored Procedure Myths
See also: http://www.ddj.com/articles/2000/0065/0065e/0065e.htm?topic=database, Article from IBM
Stored Procedures vs ad-hoc SQL
To SP or not to SP in SQL Server
TheServerSide Debates: Stored Procedures v Parameterized Queries
Who needs Stored Procedures, anyways?
Stored procedures are bad, m'kay?
Using parameterized SQL queries
( Topic last updated: 2009.09.17 12:53:06 AM )