Another reason to use Stored Procedures
-
Not to reopen the same can of worms :), but another reason I thought about last night was that if you use SPs instead of dynamic SQL, you have a way to further optimize things in the field without shipping a new app to the client. I do not remember seeing that point mentioned in the
_umpteen-hundreds_of messages that thread contains! :) That may be good reason or a bad one, but it is a reason...-=- James
If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
DeleteFXPFiles & CheckFavorites (Please rate this post!)It is always a good idea to separate data access code from business logic. One way is the use of SPs. My suggestion is to use an O/R Mapping tool like OpenAccess or NHibernate. The advantage is to have an OO interface which gives you a lot of more flexibility and gives you also the freedom to use other database servers. But I see the point in this case you must provide a new data access dll and not only a new database schema. -Detlef
-
Not to reopen the same can of worms :), but another reason I thought about last night was that if you use SPs instead of dynamic SQL, you have a way to further optimize things in the field without shipping a new app to the client. I do not remember seeing that point mentioned in the
_umpteen-hundreds_of messages that thread contains! :) That may be good reason or a bad one, but it is a reason...-=- James
If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
DeleteFXPFiles & CheckFavorites (Please rate this post!)Yeah, this goes a long way at times. Often used to optimize or even to some table changes without having to modify code. While table changes usually end up in code changes (at least for added data), there are times when a simple change to a SP will do the job and the application can keep on going. If you keep all your queries in SPs, you can even split tables and only need to change the SPs as long as the parameters stay the same to the SP. I think on of the most important features though is for optimizing. When you find some routines that are running slow, it is handy to be able to change the methods of complex SPs combined with possible index changes or temp tables to help solve bottlenecks. Then there are the other times when you need to interface to a database of another application. If they use all SP, it can make your life much easier as you can see clearly how they handle their data. SPs are not the end all for every use type of thing, but they sure are handy in most situations. I guess that is why most leading DBs use them :) BTW, if you have not use the new CLR server side code, it is pretty handy too although much more closed since it is server side code in a DLL.
Rocky <>< Latest Code Blog Post: SQL Server Express Warnings & Tips Latest Tech Blog Post: USA City Burnt To Death...
-
Not to reopen the same can of worms :), but another reason I thought about last night was that if you use SPs instead of dynamic SQL, you have a way to further optimize things in the field without shipping a new app to the client. I do not remember seeing that point mentioned in the
_umpteen-hundreds_of messages that thread contains! :) That may be good reason or a bad one, but it is a reason...-=- James
If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
DeleteFXPFiles & CheckFavorites (Please rate this post!) -
No, there is a longer thread previously that I participated in regarding reasons to or not too use SPs. This was another reason that I did not think of until later on. Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFiles -
No, there is a longer thread previously that I participated in regarding reasons to or not too use SPs. This was another reason that I did not think of until later on. Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFiles -
Not to reopen the same can of worms :), but another reason I thought about last night was that if you use SPs instead of dynamic SQL, you have a way to further optimize things in the field without shipping a new app to the client. I do not remember seeing that point mentioned in the
_umpteen-hundreds_of messages that thread contains! :) That may be good reason or a bad one, but it is a reason...-=- James
If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
DeleteFXPFiles & CheckFavorites (Please rate this post!)I once wrote an application using 95% stored procedures. Well, I didn't use all stored procs so we granted a couple people dataWriter rights. The user decided to use Access to update tables and right his own SQL. From that point on dataWriter = denied and only stored procs were used.
-
I meant that your reason is really logic encapsulation. Separating the database logic (for that operation) to the stored procedure.
Encapsulation is one reason to do it. But my point was not specifically regarding encapsulation (encapsulation just for the sake of encapsulation is a bad thing to do, IMHO). It was to provide an example where using SPs (not specifically encapsulation), provides a benefit over raw/dynamic SQL directly within an application. Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFiles -
Encapsulation is one reason to do it. But my point was not specifically regarding encapsulation (encapsulation just for the sake of encapsulation is a bad thing to do, IMHO). It was to provide an example where using SPs (not specifically encapsulation), provides a benefit over raw/dynamic SQL directly within an application. Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFilesI am not arguing the point that SPs are the better way to go. My point was that SPs usually encapsulate the data operations that are not specific to your app. An inline SQL statement would require a recompile and a distribution update. If your boss doesn't like SPs, another option would be a XML file that contains the SQL statements (obviously encrypted). But that is little help (if not a step backward) from actually placing it in the code.
-
Not to reopen the same can of worms :), but another reason I thought about last night was that if you use SPs instead of dynamic SQL, you have a way to further optimize things in the field without shipping a new app to the client. I do not remember seeing that point mentioned in the
_umpteen-hundreds_of messages that thread contains! :) That may be good reason or a bad one, but it is a reason...-=- James
If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
DeleteFXPFiles & CheckFavorites (Please rate this post!)That's the biggest reason not to use stored procedures! How can you be sure the change will get into change control? And populated out to other clients as necessary? Nothing should get out a client without going through the proper review and testing procedure!
-
James R. Twine wrote:
another reason I thought about last night was that if you use SPs instead of dynamic SQL, you have a way to further optimize things in the field without shipping a new app to the client
Only if the SP interface doesn't change in anyway. 9 times out of 10, you're going to end up having to ship out code changes as well.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.If it requires changes 9 times out of 10 then perhaps things aren't being designed so well.
Todd Smith
-
Not to reopen the same can of worms :), but another reason I thought about last night was that if you use SPs instead of dynamic SQL, you have a way to further optimize things in the field without shipping a new app to the client. I do not remember seeing that point mentioned in the
_umpteen-hundreds_of messages that thread contains! :) That may be good reason or a bad one, but it is a reason...-=- James
If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
DeleteFXPFiles & CheckFavorites (Please rate this post!)Another one is security within the database. In sql server as long as stored procedures don't use dynamic sql you can manage access rights to the procedures without giving clients access to the data tables directly. That way you can prevent them from writing custom queries on the data.
Using the GridView is like trying to explain to someone else how to move a third person's hands in order to tie your shoelaces for you. -Chris Maunder
-
If it requires changes 9 times out of 10 then perhaps things aren't being designed so well.
Todd Smith
The design may be OK - the monkeys that did the implementation... well, that is another story! :) Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFiles -
That's the biggest reason not to use stored procedures! How can you be sure the change will get into change control? And populated out to other clients as necessary? Nothing should get out a client without going through the proper review and testing procedure!
PIEBALDconsult wrote:
How can you be sure the change will get into change control? And populated out to other clients as necessary?
A valid point. But just because the change is not part of the deliverable to all other clients does not mean that is is not being tracked. Also, not all DB-based projects are limited to their own database. Some have to act as add-ons to existing schemas. Not all optimizations or changes, which may be specific to a particular client's environment, are suitable for all other clients. I am not talking about the general bug fix scenario here. The original discussion was about possible benefits of SPs. This is a possible benefit, regardless of the dangers of its possible misuse. Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFiles -
PIEBALDconsult wrote:
How can you be sure the change will get into change control? And populated out to other clients as necessary?
A valid point. But just because the change is not part of the deliverable to all other clients does not mean that is is not being tracked. Also, not all DB-based projects are limited to their own database. Some have to act as add-ons to existing schemas. Not all optimizations or changes, which may be specific to a particular client's environment, are suitable for all other clients. I am not talking about the general bug fix scenario here. The original discussion was about possible benefits of SPs. This is a possible benefit, regardless of the dangers of its possible misuse. Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFiles -
If it requires changes 9 times out of 10 then perhaps things aren't being designed so well.
Todd Smith
I'm possibly a bit biased here. Most of the time I'm called in to trouble shoot (fix) problems. For instance, in one company I had to redesign a system that was taking 186 hours (yes I did say hours) to process a weeks worth of accounts. In other words, it took into the following week to process last weeks accounts. By redesigning the SP and data layer, I got this down to less than an hour - and it wasn't that hard.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before. -
James R. Twine wrote:
Please rate this message - let me know if I helped or not!
Fishing for MVP status? How tacky. (2)
-- Rules of thumb should not be taken for the whole hand.
Actually, I have had "Please rate this post" as part of my signature for quite some time now. I just recently moved it and changed the wording a bit, though. As far as fishing, if I do not deserve the vote, then do not provide one. Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFiles